New Worksheet from listbox name!

[Damocles]

Hi All

I'm currently working on a project where the user inputs data for planned trips, The userform consists of A Mulltipage, 2 tabs "NEW EVENT" & "CURRENT EVENTS".

The first tab "NEW EVENTS" comprises of 5 textboxes, - Event Name (lbEventName), Event Date (lbEventDate), Time (lbTime) Available Places (lbPlaces) and Cost (lbCost) when the user presses the SAVE button the data gets saved to a worksheet ("Events") that all works fine! but what I need it to do is when the user saves the data, it opens an excel template file (template.xls) and renames it to the name just added in the Event Name textbox!!

The following code is used to copy data to the "Events" worksheet. which works with no problems (although there are some tweaks I need to do!!)




Private Sub UserForm_Initialize()
Dim rngName As Range
Dim WS As Worksheet
Dim I As Integer

Set WS = Worksheets("Events")
For I = 1 To WS.Cells(WS.Rows.Count, 1).End(xlUp).Row Step 1
If WS.Cells(I, 1).Value <> vbNullString Then Me.lbCurrentEvents.AddItem WS.Cells(I, 1).Value
Next I


lbCurrentEvents.RowSource = "Events!A2:E" & Range("E" & Rows.Count).End(xlUp).Row
lbCurrentEvents.ListIndex = lbCurrentEvents.ListIndex
End Sub


Private Sub cmdSave_Click()

If Me.tbEventName = "" Then
Cancel = 1
MsgBox "Please Enter an Event Name!"
Me.tbEventName.SetFocus
Exit Sub
End If

If Me.tbEventDate = "" Then
Cancel = 1
MsgBox "Please Enter an Event Date!"
Me.tbEventDate.SetFocus
Exit Sub
End If

If Me.tbTime = "" Then
Cancel = 1
MsgBox "Please Enter an Event Time!"
Me.tbTime.SetFocus
Exit Sub
End If

If Me.tbPlaces = "" Then
Cancel = 1
MsgBox "Enter number of Places available!"
Me.tbPlaces.SetFocus
Exit Sub
End If

If Me.tbCost = "" Then
Cancel = 1
MsgBox "Enter Cost!"
Me.tbPlaces.SetFocus
Exit Sub



GoTo ending


Else

'Copy input values to sheet.
Dim lRow As Long
Dim WS As Worksheet
Dim MyCell As Range, MyRange As Range
Set WS = Worksheets("Events")

lRow = WS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With WS
.Cells(lRow, 1).Value = Me.tbEventName.Value
.Cells(lRow, 2).Value = Me.tbEventDate.Value
.Cells(lRow, 3).Value = Me.tbPlaces.Value
.Cells(lRow, 4).Value = Me.tbTime.Value
.Cells(lRow, 5).Value = Me.tbCost.Value

End With
'Clear input controls.
Me.tbEventName.Value = ""
Me.tbEventDate.Value = ""
Me.tbPlaces.Value = ""
Me.tbTime.Value = ""
Me.tbCost.Value = ""

End If


ending:
lbCurrentEvents.RowSource = "Events!A2:E" & Range("E" & Rows.Count).End(xlDown).Row
lbCurrentEvents.ListIndex = lbCurrentEvents.ListIndex
End Sub



The reason for opening and renaming the template because the way it is done at the moment is that the user opens the Template spreadsheet and renames it manually. (the template stucture consists of other details of the trip, only the above details change!, hence the template!) I have been searching and come across similiar posts but not for opening and renaming a worksheet when the sheet name is a variable!

any help glady appreciated, (or perhaps a better way round to do it!)

D

[SamT]

Workbooks("TemplateName.xls").SaveAs myPath & "\" & Me.EventName.Value & ".xls"

[Damocles]

Hi SamT
Thanks for the help, When I run the code I get an error message 'subscript out of range' I changed the "TemplateName" to my template name ('Trips.xls')....

[snb]

This isn't correct:


Private Sub UserForm_Initialize()
Dim rngName As Range
Dim WS As Worksheet
Dim I As Integer

Set WS = Worksheets("Events")
For I = 1 To WS.Cells(WS.Rows.Count, 1).End(xlUp).Row Step 1
If WS.Cells(I, 1).Value <> vbNullString Then Me.lbCurrentEvents.AddItem WS.Cells(I, 1).Value
Next I


lbCurrentEvents.RowSource = "Events!A2:E" & Range("E" & Rows.Count).End(xlUp).Row
lbCurrentEvents.ListIndex = lbCurrentEvents.ListIndex
End Sub
It should be:


Private Sub UserForm_Initialize()
lbCurrentEvents.list= sheets("Events").cells(1).currentregion.columns(1).value
End Sub

[Damocles]

Hi SNB

The reason for the top code is that I have a listbox that returns 5 columns with headers 'EVENT NAME' 'EVENT DATE' 'PLACES' 'TIME' & 'COST' your code will only return the first column! the listbox is on multipage2 which recieves the data from the first multipage when user inputs data into the textboxes! so it needs to populate with the Events... However.. I'm open to any ideas' if its easier..

[snb]

In that case it's even simpler:


Private Sub UserForm_Initialize()
lbCurrentEvents.list= sheets("Events").cells(1).currentregion.value
End Sub

[Damocles]

Thank you very much!!

:bow:

....Could you help me with the original problem by any chance?

[snb]

Don't use textboxes to enter

- dates (in Excel & VBA numbers)
- times (in Excel & VBA numbers)
- costs (inherent numbers)

Please redesign your userform and post it here.

[Damocles]

Don't use textboxes to enter

- dates (in Excel & VBA numbers)
- times (in Excel & VBA numbers)
- costs (inherent numbers)



:dunno is there another way without using the textboxes? I was under the impression that textboxes were used to capture the data... (but im not the excel Guru!!:crying:)
Could you suggest another way then!

Thanks for your help, totally invaluable!!

[snb]

Repeat

Please redesign your userform and post it here.

[Damocles]

Sorry snb.. I'm lost now!! how would data on the userform be input if not using textboxes?
(As you gather I'm a noob at VBA)
So far, I have made the userform from looking online folowing tutorials etc.

16500

[SamT]

is there another way without using the textboxes? I was under the impression that textboxes were used to capture the data...
All Values in UserForm Controls are Strings. In order to use them anywhere else they must be converted to the appropriate Type first.
It may take a bit more than just

With WS
.Cells(lRow, 1).Value = Me.tbEventName.Value
.Cells(lRow, 2).Value = CDate(Me.tbEventDate.Value)
.Cells(lRow, 3).Value = Me.tbPlaces.Value
.Cells(lRow, 4).Value = TimeValue(Me.tbTime.Value)
.Cells(lRow, 5).Value = CDbl(Me.tbCost.Value)

End With

You have no data validation checks. Are "9," "9a," "900," "9 30," 9:30," "9.30p," and "21:30" all allowed in the Time Box? Only one of them is a valid DateTime.

9AM
9:30AM
09:00
09:30

Are 7/4/16 and 4/7/16 allowed in the Date box?
"Apr 7, 16" and "7 Apr 16" are not ambiguous.

[Damocles]

Hi SamT
Yes, you are correct, that was on my 'to learn' list! is this where I would use...



If IsDate...
If IsNumeric...


Thanks for your help guys!

[SamT]

Not really, you already expect those to be dates and numbers

I would set the ControlTipTexts to "4 Apr, 2016" and "09:30". You could also use the Date and Now Functions to set the ControlTipTexts to the current Date and Times in the Form_Intialize sub.
Then

Private Sub tbEventDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)

On error Goto ErrMsg
Me.tbEventDate = Format(Cdate(Me.tbEventDate), "d mmm, yyyy")

Exit Sub
ErrMsg
MsgBox "Please enter the date in the format '4 Apr, 2016.'"

With Me.tbEventDate
.Value = "4 Apr, 2016"
.Set Focus
End With
End Sub
Then

Private Sub tbTime_Exit(ByVal Cancel As MSForms.ReturnBoolean)

On Error Goto errMsg
Me.tbTime = Format(TimeValue(Me.tbTime), "hh:mm")

Exit Sub
ErrMsg
MsgBox "Please enter the time in 24 hour format like '09:30'"

With Me.tbTime
.Value = "09:30"
.Set Focus
End With
End Sub

Finally

With WS
.Cells(lRow, 1).Value = Me.tbEventName
.Cells(lRow, 2).Value = CDate(Me.tbEventDate) 'Format the Sheet's Cell as desired
.Cells(lRow, 3).Value = Me.tbPlaces.Value
.Cells(lRow, 4).Value = TimeValue(Me.tbTime) 'Format the Sheet's Cell as desired
.Cells(lRow, 5).Value = CDbl(Me.tbCost)
End With

Notice that in the Data entry, I used the month name. That is for clarity, nobody mistakes "7 Apr, 2016" for the Date of The USA's Independence Day holiday. While MSO will not mistake 13/12/16 for 12/13/2016, it can't help with dates before the 12th. You can help a bit by checking if the CDate value is < Date. Date being the Function for today's date. Putting the name of the month in the box gives the User a visual check.

The process I used was to convert the Control's Value as appropriate, format it, which reconverted it back to a String, then reset the value to the formatted string. When placing the Values on the sheet, I again converted the Control's String value as appropriate for Excel. It is this way because MS uses the same UserForm App for all it's Office applications.


nb: I highly recommend that you research exactly how snb's offerings work. IMO, He understands VBA and Excel better than anyone else I have encountered. It just happens that his and my coding styles are at opposite ends of the verbosity spectrum, because I am just a hobbyist VBA'er.

[snb]

You can use a combobox, populated with valid dates to let the user choose from it. (no need to check afterewards)
The same applies to a combobox comprising only valid times.
The spinner is inherently numeric, so you could use that for 'cost'.

PS. @SamT: thank you, but I know some...

[Jan Karel Pieterse]

I wouldn't advise never to use textboxes for those entry types. Use whatever your user is most comfortable with and adapt your code to it. For example, me personally, I don't think a combobox is very convenient for date entry, I prefer a textbox into which I can type. So for me, you'd give me a textbox and add code to that box that validates my entry to see if it is a valid date.
In my experience, creating a model that works takes 20% of the time. 80% of the time is spent on creating a user-friendly userinterface.

[Damocles]

Thanks for the input guys, What I have noticed with VBA is that there are multiple routes to reach the same goal! (which for a noob is confusing!):think:

SamT - I ran the code you supplied and it returns an compile error message and highlites the ErrMsg Sub or function not defined!

Private Sub tbTime_Exit(ByVal Cancel As MSForms.ReturnBoolean)

On Error GoTo ErrMsg
Me.tbTime = Format(TimeValue(Me.tbTime), "hh:mm")

Exit Sub
ErrMsg
MsgBox "Please enter the time in 24 hour format like '09:30'"

With Me.tbTime
.Value = "09:30"
.Set Focus
End With



Jan - I hear what you are saying... from my point of view i wanted a 'quick fix' however, the userform needs to be as simple as possible due to the person(s) using it and SamT pointed out a potential problem if someone was to enter an invalid date or time! so being able to take into account any input errors helps a lot! (The userform will be used to plan trips for an old peoples home!)

[SamT]

Sorry this is so late. I got knocked off the net by a storm.

I missed the colon after "ErrMsg:" The colon makes it a line label, as in :"GoTo LineLabel".

It is possible to give a Zoom In/Out kind of functionality to the Form


Private Sub Zoomer(Direction As String)
Dim Multiplier As Double
Dim Ctrl As Object

If Direction = "In" Then Multiplier = 1.1
If Direction = "Out" Then Multiplier = 1/1.1

With Me
.Top = .Top * 1/Multiplier
.Left = Left * 1/Multiplier
.Height = .Height * Mulitplier
.Width = .width * Multiplier

For Each Ctrl in .Controls
With Ctrl
'nb: you don't want to move the top left control or the Tops of the top controls or the Lefts of the left most controls.
'I suggest setting the values of those controls' Tags to "noTopLeft," "noTop," and "noLeft," then using an If-ElseIf-Else structure.

.Top = .Top * Multiplier
.Left = Left * Multiplier
.Height = .Height * Mulitplier
.Width = .width * Multiplier
End With
Next
End With
End Sub



Gotta watch them VBA 'gurus.' They'll test you with typos. :hi:

[snb]

To illustrate:

[Jan Karel Pieterse]

@snb: TO finish that off perhaps set the MatchRequired property to true for the controls?

[offthelip]

snb:
I like your userform, very useful, I also like the way you post it as an xlsb file, a format I wasn't aware of, so thanks again.
what a great forum for learning!!

[snb]

@jkp

You are right, I have to 'guide' (=restrict) the user even more: I chose to change the comboboxstyle.

@offtl
Thanks for reacting. Nice to see there's at least someone benefiting. ;)

[Damocles]

Hi Guys, Thanks once again for your input! sorry for late reply... Still have a day job to do..unfortunately!

SamT - The Zoom option looks very interesting, How does it work and would the code Reside in a Module?

snb - That is a very impressive date entry form!!! I will definitely be looking to use that at some stage.. Idiot proof for date entry!!

(I have also include a 'Delete' button to remove the items from the listbox and worksheet when pressed!, however I'm tryin to work out the code for myself, I could just ask you guys, however; I'm a ... 'Give a man a fish, teach a man to fish...' kinda person!! :biggrin: I will post once Ive worked it out a bit more!)

Im away until Sunday eve, (but able to see via my phone) would it be ok to continue with this post, or should I start a new Post for any other issues?

Enjoy the rest of Weekend !

[Jan Karel Pieterse]

For me, the dropdown only seems to appear for the 1st, 3rd and 5th box on the form. And for the first only if I first go to another control. Very odd, because the Enter events do seem to trigger and if I put breakpoints in them the code works.

[snb]

Maybe this version acts more predictably:

[Tom Jones]

@snb,

if you choose: year 2016, month 2, you cannot choose day 29.
Can you fixit?

[snb]

I did:

[Tom Jones]

I did:

Thank you.