Need help with DTPICKER.VALUE

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
The following code works perfectly for me but I would like to improve it by making it so that if I select an ARRIVAL DATE in the future, say in the year 2017, that the default DEPARTURE DATE would then be at least that far in the future. I hope this makes sense and that there is a way to do it. Appreciate any and all replies.

Code:
Private Sub CancelCommandButton_Click()

Unload Me

End Sub

Private Sub ClearCommandButton_Click()

Call UserForm_Initialize

End Sub

Private Sub Label21_Click()

End Sub

Private Sub OKCommandButton_Click()

Dim emptyRow As Long

'Make Sheet4 Active
Sheets(4).Activate

'Determine EmptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Export Data to worksheet
Cells(emptyRow, 1).Value = NameTextBox.Value
Cells(emptyRow, 2).Value = ArrivalDTPicker.Value
Cells(emptyRow, 3).Value = DepartureDTPicker.Value
Cells(emptyRow, 4).Value = BookingDTPicker.Value
Cells(emptyRow, 5).Value = AdultsComboBox.Value
Cells(emptyRow, 6).Value = ChildrenComboBox.Value
Cells(emptyRow, 7).Value = DepositTextBox.Value
Cells(emptyRow, 8).Value = OrderNumberTextBox.Value
Cells(emptyRow, 9).Value = CreditCardComboBox.Value
Cells(emptyRow, 10).Value = CardNumberTextBox.Value
Cells(emptyRow, 11).Value = UCase(CardNameTextBox.Value) 'Put card name in upper case on worksheet
Cells(emptyRow, 12).Value = MonthComboBox.Value
Cells(emptyRow, 13).Value = YearComboBox.Value
Cells(emptyRow, 14).Value = SecurityTextBox.Value
Cells(emptyRow, 15).Value = PhoneTextBox.Value
Cells(emptyRow, 16).Value = AddressTextBox.Value
Cells(emptyRow, 17).Value = CityTextBox.Value
Cells(emptyRow, 18).Value = UCase(StateTextBox.Value) 'Put state name in upper case on worksheet
Cells(emptyRow, 19).Value = UCase(PostalTextBox.Value) 'Put postal name in upper case on worksheet
Cells(emptyRow, 20).Value = CountryComboBox.Value
Cells(emptyRow, 21).Value = EmailTextBox.Value
Cells(emptyRow, 22).Value = WebsiteComboBox.Value

End Sub

Private Sub UserForm_Initialize()

'Empty NameTextBox
NameTextBox.Value = ""

'Empty AddressTextBox
AddressTextBox.Value = ""

'Empty CityTextBox
CityTextBox.Value = ""

'Empty StateTextBox
StateTextBox.Value = ""

'Empty PostalTextBox
PostalTextBox.Value = ""

'Empty CountryComboBox
CountryComboBox.Value = ""

'Fill CountryComboBox
With CountryComboBox
    .AddItem "US"
    .AddItem "CA"
    .AddItem "EU"
    .AddItem "SA"
    .AddItem "AU"
    .AddItem "AF"
    .AddItem "AS"
End With

'Empty PhoneTextBox
PhoneTextBox.Value = ""

'Empty EmailTextBox
EmailTextBox.Value = ""

'Default Date to Todays Date
ArrivalDTPicker.Value = Date
DepartureDTPicker.Value = Date
BookingDTPicker.Value = Date

'Empty AdultsComboBox
AdultsComboBox.Value = ""

'Fill AdultsComboBox
With AdultsComboBox
    .AddItem "1"
    .AddItem "2"
    .AddItem "3"
    .AddItem "4"
    .AddItem "5"
    .AddItem "6"
End With

'Empty ChildrenComboBox
ChildrenComboBox.Value = ""

'Fill ChildrenComboBox
With ChildrenComboBox
    .AddItem "0"
    .AddItem "1"
    .AddItem "2"
    .AddItem "3"
    .AddItem "4"
    .AddItem "5"
End With

'Empty CreditCardComboBox
CreditCardComboBox.Clear

'Fill CreditCardComboBox
With CreditCardComboBox
    .AddItem "Visa"
    .AddItem "MasterCard"
    .AddItem "Discover"
End With

'Empty CardNumberTextBox
CardNumberTextBox.Value = ""

'Empty CardNameTextBox
CardNameTextBox.Value = ""
' Me is optional
Range("A1").Value = UCase(Me.CardNameTextBox.Text)

'Empty SecurityTextBox
SecurityTextBox.Value = ""

'Empty MonthComboBox
MonthComboBox.Clear

'Fill MonthComboBox
With MonthComboBox
    .AddItem "01"
    .AddItem "02"
    .AddItem "03"
    .AddItem "04"
    .AddItem "05"
    .AddItem "06"
    .AddItem "07"
    .AddItem "08"
    .AddItem "09"
    .AddItem "10"
    .AddItem "11"
    .AddItem "12"
End With

'Empty YearComboBox
YearComboBox.Clear

'Fill YearComboBox
With YearComboBox
    .AddItem "15"
    .AddItem "16"
    .AddItem "17"
    .AddItem "18"
    .AddItem "19"
    .AddItem "20"
    .AddItem "21"
    .AddItem "22"
End With

'Empty DepositTextBox
DepositTextBox.Value = ""

Me.DepositTextBox.Value = "$"
    DepositTextBox.EnterFieldBehavior = fmEnterFieldBehaviorRecallSelection
    
'Empty OrderNumberTextBox
OrderNumberTextBox.Value = ""

'Empty WebsiteComboBox
WebsiteComboBox.Clear

'Fill WebsiteComboBox
With WebsiteComboBox
    .AddItem "airbnb"
    .AddItem "flipkey"
    .AddItem "homeaway"
    .AddItem "housetrip"
    .AddItem "trip advisor"
    .AddItem "vacation rentals"
    .AddItem "vrbo"
End With

'Set Focus on NameTextBox
NameTextBox.SetFocus

End Sub

Private Sub DepositTextBox_AfterUpdate()
    If IsNumeric(DepositTextBox.Value) Then
        'Format as currency
        DepositTextBox.Value = Format(DepositTextBox.Value, "$#,##0.00")
    Else
        DepositTextBox.Value = "$"
    End If
End Sub
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

mhillmann

Board Regular
Joined
Mar 30, 2015
Messages
154
You could always just make the Departure date dependent on the Arrival date.

Something like:

Code:
Private Sub ArrivalDTPicker_Change()
      DepartureDTPicker = ArrivalDTPicker
End Sub

This could lead to some unwanted circumstances though, like if you decide to change the arrival date last, it will change the departure date as well.

Sorry I can't help more.
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
Thank you mhillmann, that works perfectly for me. I really appreciate your help!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,331
Messages
5,624,050
Members
416,007
Latest member
csf

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
Top