user inputs with Message Boxes

[Erik]

Love this forum...you guys have helped me out so much. Here's another one,
but could not find an answer.

I need to write a macro that will prompt the user to input a name, starting
time, starting date, ending time, and ending date. Then take those inputs
and insert them into a cell with other text in it. I need it to look
something like this...

Report on name starting at start time start date to end time end date.

Thanks for any help. I really appreciate it, and my users will appreciate
it even more.

Erik

 

[Harald Staff]

Hi Erik

Don't. A series of input boxes doesn't show the whole set of entries and has
no Back functionality. It is an awful user experience. Create a userform for
this job:

http://www.contextures.com/xlUserForm01.html

HTH. Best wishes Harald

 

[Erik]

Harold,
Understand the userform deal and will try to incorporate it on Monday when I
go back to work.

Will I be able to use this and have it insert the user inputs into a
sentence like in my original post?

Also, how do I get the userform to show up after(or before) I run the other
macros to import data and format the spreadsheet?

Thanks again

Erik

Erik

 

[Harald Staff]

Yes to all, it'll work. Deal with "how" after reading and testing userforms.

Best wishes Harald

 

[Erik]

Harald,
Ok, got the userform to work. Now I need to modify, just a bit and that is
where I am getting stuck. Below is the code I have so far, but running into
errors.

Private Sub cmdAdd_Click()
Dim ws As Worksheet
Dim cty As String
Dim sDate As String
Dim eDate As String
Set ws = Worksheets("Sheet1")
Set cty = Me.txtCity.Value
Set sDate = Me.txtStart.Value
Set eDate = Me.txtEnd.Value

'goto cell A3
Range("A3").Select


'put the data to the database in the selected cells which will be merged

ActiveCell.Range("A1:E1").Select
ActiveCell.FormulaR1C1 = "SUBJ: Water usage in " & cty "from 12:00pm "
&sDate& "to 11:59pm " &eDate& " See Ted for more information."



With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = True
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.Font.Bold = True


'clear the data
Me.txtCity.Value = ""
Me.txtStart.Value = ""
Me.txtEnd.Value = ""

Me.txtCity.SetFocus
Unload Me

End Sub

Any help you can provide is appreciated. Thanks

 

[Susan]

what are the errors & where are they occuring?

susan