Updating DTPicker with a variable

KB_Miner

Board Regular
Joined
Dec 5, 2013
Messages
119
I'm trying to use multiple DTPickers within a form. When the user clicks on the submit button, I would like the form to copy the end time, and when the form refreshes, put the end time as the new start time.

I've declared the variable LastTime as a public variable as I would like to reset it when the workbook is opened. To accomplish that, here's my current code in ThisWorkbook under general declarations:

Option Explicit
Public LastTime As String

Private Sub Workbook_Open()
LastTime = "12:00:00 AM" 'Reset variable to default time

frmTimeInput.Show 'Open Form when workbook opens
End Sub

Near the end of my code for the submit button, I have a line of code that sets the LastTime variable to the value of the end time DTPicker (in my code it's DTPicker3, and the start time is DTPicker2). Since it's a public variable, I do not have it as a declared variable within the code for the submit button. After this line of code, I'm using Unload Me to reset the form, followed by frmTimeInput.Show to redisplay the form. To try and use the variable to update the time, I'm trying this code, but it doesn't seem to be working. When I debug it, it shows LastTime as empty within the code below, so not sure why it's not grabbing the value from the public variable as during debugging I can see the LastTime variable is set to 12 AM in the workbook open sub.

Private Sub UserForm_Activate()
DTPicker2.Value = LastTime

End Sub

I'm not sure if the problem is in my variables, or just in how I'm trying to set values during the activation of the form.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Would recommend to use Option Explicit in all your code modules.
Try the below ...

Rich (BB code):
Private Sub UserForm_Activate()
    DTPicker2.Value = ThisWorkbook.LastTime
End Sub
 
Upvote 0
Would recommend to use Option Explicit in all your code modules.
Try the below ...

Rich (BB code):
Private Sub UserForm_Activate()
    DTPicker2.Value = ThisWorkbook.LastTime
End Sub
That helped. I now see the variable having the 12AM time. There's still an issue in the code somewhere that I'm thinking has to do with how I'm trying to reload the form after hitting the button as the end time is not yet becoming the new start time.
 
Upvote 0
You are using the default instance of the userform's class module, rather than "New-ing" it, with which you (unconsciously) leave a number of possibilities unused.
If you're able to provide both the code which launches your userform and the code of the userform itself, I'm willing to try to determine where the shoe pinches.
 
Upvote 0
ThisWorkbook is a class module, so Public LastTime As String declares LastTime as a member of that class, not as a global variable.

To read or write the LastTime variable from another class or module, either qualify it with its class name, ThisWorkbook.LastTime, or move Public LastTime As String to a standard module so that it is a true global variable.
 
Upvote 0
I was able to get my code working. I moved the setting of the variable to my code for the submit button. I also now load the form before showing it so that I can manipulate values before showing the form. New code is below:

VBA Code:
LastTime = TimeValue(DTPicker3.Value)
Unload Me

Load frmTimeInput
frmTimeInput.DTPicker1.Value = Date
frmTimeInput.DTPicker2.Value = LastTime
frmTimeInput.Show
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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