DTPicker problem on Multipage Userform

[inarbeth]

I am creating a multipage userform that contains DTPicker controls on more than one page. The controls are linked to dates in two named ranges (of one cell each) called moeDateExchanged and csContractDate. When the userform is launched I want to the DTPickers to show the dates in the named ranges. (In other words the user can pick up where he left off.) In my Userform_Initialize code I include

Private Sub Userform_Initialize()
moeDTPicker1.Value = Range("moeDateExchanged").Value
csDTPicker1.Value = Range("csContractDate").Value
End Sub

However, this causes a runtime error:
'35788'
An error occurred in a call to the Windows Date and Time Picker control.

moeDTPicker1 is on the first page of the Multipage and csDTPicker1 is on the second page. If I comment out:

 'csDTPicker1.Value = Range("csContractDate").Value

the form launches fine but if it is in the code (and even if I comment out

'moeDTPicker1.Value = Range("moeDateExchanged").Value

I get the error. Can anyone explain this or provide a workaround?

 

[rs2k]

Have you tried putting in the fullpath?

frmxxxx.multipage1.moeDTPicker1
frmxxxx.multipage.csDTPicker1

Where frmxxx is the name of your form and multipage1 is the name of the multipage control,

Just a thought, HTH
Colin

 

[inarbeth]

Have you tried putting in the fullpath?

frmxxxx.multipage1.moeDTPicker1
frmxxxx.multipage.csDTPicker1

Where frmxxx is the name of your form and multipage1 is the name of the multipage control,

Just a thought, HTH
Colin

No it doesn't. VBA does not recognise multipage though it does multipage1.

 

[rs2k]

Ok, perhaps it was a bit of a vague reply I gave. Here is some sample code that I use on one of my workbooks adapted for your needs.

frmName.Multipage1.Pages("Page1").moeDTPicker1.value=Sheets("Sheet1").Range("moeDateExchanged").Value
frmName.Multipage1.Pages("Page2").csDTPicker1.value=Sheets("Sheet2").Range("csContractDate").Value

frmName change to the name of your form
Multipage1.Pages("Page1") change the ("Page1") bit to the page the calendar is on
Sheets("Sheet1") change ("Sheet1") bit to the name of your worksheet

HTH
Colin

 

[inarbeth]

Still getting the same error. However, if I comment out the line with moeDTPicker (which is on the first page) and leave the line with csDTPicker (which control is on the second page) I get this runtime error:

Run-time error '2110':
Can't move focus to the control because it is invisible, not enabled or of a type that does not accept the focus.

 

[rs2k]

So if you comment out the second line (Page2), does it run OK?

How about splitting the code and placing the relevant bit of code with the relevant page, I cant remember if each page has a got focus event or not. You may have to mess about with IF/THEN statements to see which page has got focus and run the relevant bit of code.

I'm on my way out of the office now, but will look in later, can you post the code you have,

Cheers
Colin

 

[inarbeth]

rs2k thanks for all your help.
The code I have is:

Private Sub Userform_Initialize()
MemoOfExchange.MultiPage1.Pages("Page1").moeDTPicker1.Value = Sheets("Exchange memorandum").Range("moeDateExchanged").Value
MemoOfExchange.MultiPage1.Pages("Page2").csDTPicker1.Value = Sheets("Completion Statement").Range("csContractDate").Value
End Sub

"So if you comment out the second line (Page2), does it run OK?"

The answer is, it depends. If when in the VBE I close leaving Page1 selected, it works. Otherwise not.
And vice versa. In other words there seems to be a problem switching focus. If I leave the VBE on page2 and the code relates to csDTPicker1 it runs fine.
I tried amending the code to

Private Sub Userform_Initialize()
MemoOfExchange.MultiPage1.Pages("Page1").moeDTPicker1.Value = Sheets("Exchange memorandum").Range("moeDateExchanged").Value
MemoOfExchange.MultiPage1.Pages("Page2").SetFocus
MemoOfExchange.MultiPage1.Pages("Page2").csDTPicker1.Value = Sheets("Completion Statement").Range("csContractDate").Value
End Sub

but then get Run-time error 438
Object doesn't support this property or method

 

[inarbeth]

Bump.
rs2k or anyone else, can you help with this please?

 

[rs2k]

Hi inarbeth,

Sorry, got side tracked, I'm guessing you must have some other code in your workbook that is corrupting it.

I created a simple workbook with 3 sheets, 'Main', 'Exchange memorandom' and 'Completion Statement' to follow your example, I then used A1 on 'Exchange memorandom' and 'Completion Statement' and defined their names the same as yours to use as dates. I created a userform, again using your naming convention with a multipage and calendars on each page. I added the first bit of code to the initialialize event;

Private Sub Userform_Initialize()
MemoOfExchange.MultiPage1.Pages("Page1").moeDTPicker1.Value = Sheets("Exchange memorandum").Range("moeDateExchanged").Value
MemoOfExchange.MultiPage1.Pages("Page2").csDTPicker1.Value = Sheets("Completion Statement").Range("csContractDate").Value
End Sub

The sheet 'Main' was created as a generic sheet as I'm not sure how you are launching your form, all it has is a simple button to launch the userform.

So in summary, I haven't changed any code and it works!

If you PM your email I will send you the workbook.

HTH
Colin