Using VBA code on a userform to fill a textbox with a number

F

Fanciesmom

New Member
Joined
Feb 15, 2013
Messages
3
I am beginning to wonder if I am asking the impossible here but here goes.

I am new to this and to top it off I have a bit of a time constraint so bungling along until I accidentally figure this out is not going to work.

I am designing a reservation system for an RV park. I have a userform designed to gather data and post it to a worksheet. The workbook contains a worksheet for reservations, availability, long term, and group reservations. It also has monthly sheets where I eventually will sort the data from reservations and fill out the monthly sheets - but that is down the road.

Right now my immediate problem is to come up with code that will use the numeric value of TBToday (using the year?) and 4 additional numbers to display a unique "ResrvNum" in the TBResrvNum textbox. This number has to display when the form opens so that the number can be written on the paper record and used to fill out a manual chart (until I get the rest of this done).

I was going to use a concatenation formula in column AE on the Reservations worksheet - but the number has to be the first thing on the userform rather than happening after the info is written to the worksheet.


Code: 
Private Sub Workbook_Open()
    Worksheets("trying new things test").Activate
    FrmReservations.Show
End Sub


Private Sub ComOK_Click()
    Dim Rowcount As Long
    Dim Ctl As Control
    Dim ws As Worksheet


' Check user input


    If TBToday.Value = "" Then
        MsgBox "Please Enter Today's Date!"
        TBToday.SetFocus
        Exit Sub
End If
    If CBEmp.Value = "" Then
        MsgBox "Please Select Employee ID"
        CBEmp.SetFocus
        Exit Sub
End If
    If CBSite.Value = "" Then
        MsgBox "Please Select an Available Site"
        CBSite.SetFocus
        Exit Sub
End If
    If TBLastName.Value = "" Then
        MsgBox "Please Enter a Last Name"
        TBLastName.SetFocus
        Exit Sub
End If
    If TBFirstName.Value = "" Then
        MsgBox "Please Enter a First Name"
        TBFirstName.SetFocus
        Exit Sub
End If
    If TBAddress.Value = "" Then
        MsgBox "Please Enter an Address"
        TBAddress.SetFocus
        Exit Sub
End If
    If TBCity.Value = "" Then
        MsgBox "Please Enter a City"
        TBCity.SetFocus
        Exit Sub
End If
    If TBState.Value = "" Then
        MsgBox "Please Enter a State"
        TBState.SetFocus
        Exit Sub
End If
    If TBZipcode.Value = "" Then
        MsgBox "Please Enter a Zip Code"
        TBZipcode.SetFocus
        Exit Sub
End If
    If TBPhone.Value = "" Then
        MsgBox "Please Enter a Phone Number"
        TBPhone.SetFocus
        Exit Sub
End If
    If TBCell.Value = "" Then
        MsgBox "Please Enter a Cell Phone Number"
        TBCell.SetFocus
        Exit Sub
End If
    If TBEmail.Value = "" Then
        MsgBox "Please Enter an Email Address"
        TBEmail.SetFocus
        Exit Sub
End If
    If TBArrival.Value = "" Then
        MsgBox "Please Enter an Arrival Date"
        TBArrival.SetFocus
        Exit Sub
End If
    If TBDepart.Value = "" Then
        MsgBox "Please Enter a Departure Date"
        TBDepart.SetFocus
        Exit Sub
End If
    If TBDays.Value = "" Then
        MsgBox "Please Enter How Many Days"
        TBDays.SetFocus
        Exit Sub
End If
    If CBKind.Value = "" Then
        MsgBox "Please Choose the Kind"
        CBKind.SetFocus
        Exit Sub
End If
    If CBAdults.Value = "" Then
        MsgBox "Please Enter Number of Adults"
        CBAdults.SetFocus
        Exit Sub
End If
    If CBKids.Value = "" Then
        MsgBox "Please Enter Number of Children"
        CBKids.SetFocus
        Exit Sub
End If
    If CBVehicles.Value = "" Then
        MsgBox "Please Enter Number of Vehicles"
        CBVehicles.SetFocus
        Exit Sub
End If
    If CBPower.Value = "" Then
        MsgBox "Please Select Power Needed"
        CBPower.SetFocus
        Exit Sub
End If
    If CBPets.Value = "" Then
        MsgBox "Please Check if There Are Pets"
        CBPets.SetFocus
End If


' Write data to worksheet
    Rowcount = Worksheets("Reservations").Range("A" & Rows.Count).End(xlUp).Row + 1
        With Worksheets("Reservations")
        .Cells(Rowcount, "A").Value = TBToday.Value
        .Cells(Rowcount, "B").Value = CBEmp.Value
        .Cells(Rowcount, "C").Value = CBSite.Value
        .Cells(Rowcount, "D").Value = TBLastName.Value
        .Cells(Rowcount, "E").Value = TBFirstName.Value
        .Cells(Rowcount, "F").Value = TBAddress.Value
        .Cells(Rowcount, "G").Value = TBCity.Value
        .Cells(Rowcount, "H").Value = TBState.Value
        .Cells(Rowcount, "I").Value = TBZipcode.Value
        .Cells(Rowcount, "J").Value = TBPhone.Value
        .Cells(Rowcount, "K").Value = TBCell.Value
        .Cells(Rowcount, "L").Value = TBEmail.Value
        .Cells(Rowcount, "M").Value = TBArrival.Value
        .Cells(Rowcount, "N").Value = TBDepart.Value
        .Cells(Rowcount, "O").Value = TBDays.Value
        .Cells(Rowcount, "P").Value = CBKind.Value
        .Cells(Rowcount, "Q").Value = CBAdults.Value
        .Cells(Rowcount, "R").Value = CBKids.Value
        .Cells(Rowcount, "S").Value = CBVehicles.Value
        .Cells(Rowcount, "T").Value = CBPower.Value
        .Cells(Rowcount, "U").Value = CBPets.Value
        .Cells(Rowcount, "V").Value = CBLongTerm.Value
        .Cells(Rowcount, "W").Value = CBGroup.Value
        .Cells(Rowcount, "X").Value = CBRecBldg.Value
        .Cells(Rowcount, "Y").Value = CBSites.Value
        .Cells(Rowcount, "Z").Value = CBPkg.Value
        .Cells(Rowcount, "AA").Value = TBNotes.Value
    End With
    
'Clear the form for the next entry
    Me.TBToday.Value = ""
    Me.CBEmp.Value = ""
    Me.CBSite.Value = ""
    Me.TBLastName.Value = ""
    Me.TBFirstName.Value = ""
    Me.TBAddress.Value = ""
    Me.TBCity.Value = ""
    Me.TBState.Value = ""
    Me.TBZipcode.Value = ""
    Me.TBPhone.Value = ""
    Me.TBCell.Value = ""
    Me.TBEmail.Value = ""
    Me.TBArrival.Value = ""
    Me.TBDepart.Value = ""
    Me.TBDays.Value = ""
    Me.CBKind.Value = ""
    Me.CBAdults.Value = ""
    Me.CBKids.Value = ""
    Me.CBVehicles.Value = ""
    Me.CBPower.Value = ""
    Me.CBPets.Value = ""
    Me.CBLongTerm.Value = ""
    Me.CBGroup.Value = ""
    Me.CBRecBldg.Value = ""
    Me.CBSites.Value = ""
    Me.CBPkg.Value = ""
    Me.TBNotes.Value = ""
    Me.TBToday.SetFocus
        
End Sub


Private Sub ComCancel_Click()
    Unload Me
End Sub
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
What exactly do you want in TBToday and where would the number you want to add to it come from?
 
Upvote 0
Hi Norie TBToday is the date that the form is filled out - the person behind the counter will enter Today (date). I would like the number in TBRservNum to have the last two digits of the date 02/05/2013 and the other 4 digits I am not sure about how best to create them. I did have the row number and the numeric value of the date - (if the next record was in row 11 and the date was 02/15/2013 the number would be 4132011) Can the userform get the next row number in the spreadsheet before the data is written?

I have a feeling I am making this too complicated?
 
Upvote 0
You could put the current date in TBToday automatically when the form opens with code like this.

I've added code for putting the 2 digit year in TBServName as well, but I'm still not sure what you actually want in there.
Code: 
Private Sub UserForm_Initialize()
       TBToday.Value = Date
       TBServName.Value = Format(Date, "yy")
End Sub
 
Upvote 0
Thank you Norie! The date function works perfectly!

Now I am struggling with two issues.

1: Reservation Number: I need to have a number that increments every time the userform opens. I would like it to be a 5 digit number starting at 11000. This number needs to be generated whenever a userform is opened and can be cleared if the userform is not completed. Am I correct in thinking that clearing the TBRservNum textbox could happen with the "cancel" command button?

2: TBNightss needs to subtract TBArrival from TBDepart and display the results in TBNights. It sends it to the worksheet "reservations, but does not display it on the userform.
Code: 
Private Sub Workbook_Open()
    Worksheets("trying new things test").Activate
    FrmReservations.Show
End Sub


Private Sub ComClear_Click()


End Sub
Private Sub UserForm_Initialize()
       TBToday.Value = Date
       'TBResrvNum.Value = Format(Date, "yy")
End Sub


Private Sub ComOK_Click()
    Dim Rowcount As Long
    Dim Ctl As Control
    Dim ws As Worksheet


'Private Sub TBNights_Change()
    Dim sDate As Date
    Dim eDate As Date
    sDate = Me.TBArrival.Value
    eDate = Me.TBDepart.Value
    Me.TBDays = eDate - sDate
'End Sub


' Check user input
    If CBEmp.Value = "" Then
        MsgBox "Please Select Employee ID"
        CBEmp.SetFocus
        Exit Sub
End If
    If CBSite.Value = "" Then
        MsgBox "Please Select an Available Site"
        CBSite.SetFocus
        Exit Sub
End If
    If TBLastName.Value = "" Then
        MsgBox "Please Enter a Last Name"
        TBLastName.SetFocus
        Exit Sub
End If
    If TBFirstName.Value = "" Then
        MsgBox "Please Enter a First Name"
        TBFirstName.SetFocus
        Exit Sub
End If
    If TBAddress.Value = "" Then
        MsgBox "Please Enter an Address"
        TBAddress.SetFocus
        Exit Sub
End If
    If TBCity.Value = "" Then
        MsgBox "Please Enter a City"
        TBCity.SetFocus
        Exit Sub
End If
    If TBState.Value = "" Then
        MsgBox "Please Enter a State"
        TBState.SetFocus
        Exit Sub
End If
    If TBZipcode.Value = "" Then
        MsgBox "Please Enter a Zip Code"
        TBZipcode.SetFocus
        Exit Sub
End If
    If TBPhone.Value = "" Then
        MsgBox "Please Enter a Phone Number"
        TBPhone.SetFocus
        Exit Sub
End If
    If TBCell.Value = "" Then
        MsgBox "Please Enter a Cell Phone Number"
        TBCell.SetFocus
        Exit Sub
End If
    If TBEmail.Value = "" Then
        MsgBox "Please Enter an Email Address"
        TBEmail.SetFocus
        Exit Sub
End If
    If TBArrival.Value = "" Then
        MsgBox "Please Enter an Arrival Date"
        TBArrival.SetFocus
        Exit Sub
End If
    If TBDepart.Value = "" Then
        MsgBox "Please Enter a Departure Date"
        TBDepart.SetFocus
        Exit Sub
End If
    If CBKind.Value = "" Then
        MsgBox "Please Choose the Kind"
        CBKind.SetFocus
        Exit Sub
End If
    If CBAdults.Value = "" Then
        MsgBox "Please Enter Number of Adults"
        CBAdults.SetFocus
        Exit Sub
End If
    If CBKids.Value = "" Then
        MsgBox "Please Enter Number of Children"
        CBKids.SetFocus
        Exit Sub
End If
    If CBVehicles.Value = "" Then
        MsgBox "Please Enter Number of Vehicles"
        CBVehicles.SetFocus
        Exit Sub
End If
    If CBPower.Value = "" Then
        MsgBox "Please Select Power Needed"
        CBPower.SetFocus
        Exit Sub
End If
    If CBPets.Value = "" Then
        MsgBox "Please Check if There Are Pets"
        CBPets.SetFocus
End If


' Write data to worksheet
    Rowcount = Worksheets("Reservations").Range("A" & Rows.Count).End(xlUp).Row + 1
        With Worksheets("Reservations")
        .Cells(Rowcount, "A").Value = TBToday.Value
        .Cells(Rowcount, "B").Value = CBEmp.Value
        .Cells(Rowcount, "C").Value = CBSite.Value
        .Cells(Rowcount, "D").Value = TBLastName.Value
        .Cells(Rowcount, "E").Value = TBFirstName.Value
        .Cells(Rowcount, "F").Value = TBAddress.Value
        .Cells(Rowcount, "G").Value = TBCity.Value
        .Cells(Rowcount, "H").Value = TBState.Value
        .Cells(Rowcount, "I").Value = TBZipcode.Value
        .Cells(Rowcount, "J").Value = TBPhone.Value
        .Cells(Rowcount, "K").Value = TBCell.Value
        .Cells(Rowcount, "L").Value = TBEmail.Value
        .Cells(Rowcount, "M").Value = TBArrival.Value
        .Cells(Rowcount, "N").Value = TBDepart.Value
        .Cells(Rowcount, "O").Value = TBNights.Value
        .Cells(Rowcount, "P").Value = CBKind.Value
        .Cells(Rowcount, "Q").Value = CBAdults.Value
        .Cells(Rowcount, "R").Value = CBKids.Value
        .Cells(Rowcount, "S").Value = CBVehicles.Value
        .Cells(Rowcount, "T").Value = CBPower.Value
        .Cells(Rowcount, "U").Value = CBPets.Value
        .Cells(Rowcount, "V").Value = CBLongTerm.Value
        .Cells(Rowcount, "W").Value = CBGroup.Value
        .Cells(Rowcount, "X").Value = CBRecBldg.Value
        .Cells(Rowcount, "Y").Value = CBSites.Value
        .Cells(Rowcount, "Z").Value = CBPkg.Value
        .Cells(Rowcount, "AA").Value = TBNotes.Value
    End With
    
'Clear the form for the next entry
    Me.TBToday.Value = ""
    Me.CBEmp.Value = ""
    Me.CBSite.Value = ""
    Me.TBLastName.Value = ""
    Me.TBFirstName.Value = ""
    Me.TBAddress.Value = ""
    Me.TBCity.Value = ""
    Me.TBState.Value = ""
    Me.TBZipcode.Value = ""
    Me.TBPhone.Value = ""
    Me.TBCell.Value = ""
    Me.TBEmail.Value = ""
    Me.TBArrival.Value = ""
    Me.TBDepart.Value = ""
    Me.TBNights.Value = ""
    Me.CBKind.Value = ""
    Me.CBAdults.Value = ""
    Me.CBKids.Value = ""
    Me.CBVehicles.Value = ""
    Me.CBPower.Value = ""
    Me.CBPets.Value = ""
    Me.CBLongTerm.Value = ""
    Me.CBGroup.Value = ""
    Me.CBRecBldg.Value = ""
    Me.CBSites.Value = ""
    Me.CBPkg.Value = ""
    Me.TBNotes.Value = ""
    Me.TBToday.SetFocus
        
End Sub


Private Sub ComCancel_Click()
    Unload Me
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Trying to do an Auto Sort when I enter Tab using VBA Code
2
Replies
19
Views
587
Peter_SSs
Peter_SSs
I
  • Solved
Unable to change textbox name without code breaking
Replies
6
Views
329
ipbr21054
I
I
  • Question Question
Add button to increment textbox value by 1
2
Replies
13
Views
781
Artik
Artik
I
  • Solved
Code not formatting correctly in textbox userform
2 3
Replies
22
Views
847
MARK858
MARK858
A
  • Question Question
VBA to repeat recording data
Replies
1
Views
90
Joe4
Joe4

Forum statistics

Threads
1,225,619
Messages
6,186,049
Members
453,335
Latest member
sfd039

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top