PDA

View Full Version : Help! Excel User prompt for data input



Adrianb
07-18-2008, 01:53 AM
Hi there,
As a complete beginer to VBA, i have a question regarding data input prompt.
After opening Excel, i'd like to be able to manually run a macro to open dialogue boxes that prompt me to enter "time1", followed by "time2", then total1 and finally total2. Once these have been entered the final dialogue/prompt window would disappear and the following cells would be populated.
A1 = time1
A2 = time2
A3 = total1
A4 = total2.
Any help would be greatly appreciated
Cheers

Simon Lloyd
07-18-2008, 02:58 AM
put the first sub in the Thisworkbook module, then create a userform with 4 textboxes and a command button and paste the second code in.
Private Sub Workbook_Open()
UserForm1.Show
End Sub

Private Sub CommandButton1_Click()
With Sheets("Sheet1")
.Range("A1").Value = Format(Me.TextBox1.Value, "hh:mm")
.Range("A2").Value = Format(Me.TextBox2.Value, "hh:mm")
.Range("A3").Value = Me.TextBox3.Value
.Range("A4").Value = Me.TextBox4.Value
End With
If Me.TextBox1.Value = "" Or Me.TextBox2.Value = "" Or Me.TextBox3.Value = "" Or _
Me.TextBox4.Value = "" Then
MsgBox "Value missing, all boxes must be filled", vbOKOnly, "Missing data"
Exit Sub
End If
Unload Me
End Sub

Adrianb
07-18-2008, 03:04 AM
Many thanks Simon
I'll give it a go

JimmyTheHand
07-18-2008, 03:12 AM
Simon's solution is an advanced one. For a complete beginner (that is, if you want to learn the basics,) I would suggest a little familiarization with InputBox and Ranges, first. E.g.

Sub test()
Dim InputValue As Variant
InputValue = InputBox("Enter a value for Time1")
Range("A1") = InputValue
Range("A1").NumberFormat = "hh:mm:ss"
End Sub

And the above code repeated for Time2 and the totals.

HTH

Jimmy

Adrianb
07-18-2008, 03:23 AM
Thank Jimmy,
Another quick question.
How do i repeat the code for the rest of the inputs

JimmyTheHand
07-18-2008, 03:38 AM
Sub test()
Dim InputValue As Variant
InputValue = InputBox("Enter a value for Time1")
Range("A1") = InputValue
InputValue = InputBox("Enter a value for Time2")
Range("A2") = InputValue
InputValue = InputBox("Enter a value for Total1")
Range("A3") = InputValue
InputValue = InputBox("Enter a value for Total2")
Range("A4") = InputValue
Range("A1:A2").NumberFormat = "hh:mm:ss"
End Sub
This is very basic, mind you. I wouldn't do it this way, except for the purpose of education. There are a few ways to improve this code a little, but if you want a one-time, full extra solution, stick to Simon's.

Jimmy

Simon Lloyd
07-18-2008, 04:11 AM
One thing that should be said for both solutions is that they won't work if a user has decided not to allow macro's, also, with my code there isn't any check to see that they are indeed entering a valid time or that a valid entry (number) is being entered in totals. What is the time for? do you just want the times a user logs in to the workbook?

Adrianb
07-18-2008, 05:25 AM
Hi Simon,
The time input is literally just for a start time and current time entry, so that rather than select each cell and manually enter in the times, the macro prompts
Cheers

mdmackillop
07-18-2008, 10:02 AM
You can enter the current time as a default, or enter it directly without the need of an input box

InputValue = InputBox("Enter a value for Time2", "Current time", Format(Now(), "hh:mm:ss"))