Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Picker on Multipage
How do I access a Date picker on a Multipage?
I want to assign it a value. I have tried frmTMF.MultiPage1.Pages.Item(DTPickerReviewDate).V alue = Date and frmTMF.DTPickerReviewDate.Value = Date both return error 438 - Object doesn't support this property or method |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Picker on Multipage
Sorry needed to correct first post
How do I access a Date picker on a Multipage? I want to assign it a value. I have tried frmTMF.MultiPage1.DTPickerReviewDate.Value = Date and frmTMF.DTPickerReviewDate.Value = Date first returns error 438 - Object doesn't support this property or method second returns error 35788 - An error occured in a call to the windows date and time picker control |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Picker on Multipage
This worked for me:
Me.DTPicker1.Year = Year(Date) Me.DTPicker1.Month = Month(Date) Me.DTPicker1.Day = Day(Date) HTH, Eric "Shawn" wrote: Sorry needed to correct first post How do I access a Date picker on a Multipage? I want to assign it a value. I have tried frmTMF.MultiPage1.DTPickerReviewDate.Value = Date and frmTMF.DTPickerReviewDate.Value = Date first returns error 438 - Object doesn't support this property or method second returns error 35788 - An error occured in a call to the windows date and time picker control |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Picker on Multipage
If the page is activated
Me.DTPicker1.Value = Date If not try on change event... Private Sub MultiPage1_Change() If MultiPage1.SelectedItem.Caption = "Page2" Then Me.MultiPage1.Pages(1).DTPicker1.Value = Date End If End Sub If this post helps click Yes --------------- Jacob Skaria "Shawn" wrote: Sorry needed to correct first post How do I access a Date picker on a Multipage? I want to assign it a value. I have tried frmTMF.MultiPage1.DTPickerReviewDate.Value = Date and frmTMF.DTPickerReviewDate.Value = Date first returns error 438 - Object doesn't support this property or method second returns error 35788 - An error occured in a call to the windows date and time picker control |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Picker on Multipage
Hi Shawn,
Pls also see my post dd. 07.07.09 subject:DatePicker control lost in 2007, if it is relevant. Rgds Lemi "Shawn" wrote in message ... Sorry needed to correct first post How do I access a Date picker on a Multipage? I want to assign it a value. I have tried frmTMF.MultiPage1.DTPickerReviewDate.Value = Date and frmTMF.DTPickerReviewDate.Value = Date first returns error 438 - Object doesn't support this property or method second returns error 35788 - An error occured in a call to the windows date and time picker control |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Picker on Multipage
This does not work on a multipage on a userform - sorry if I was not clear on
that. A userform with a multipage that has a date picker in it. "EricG" wrote: This worked for me: Me.DTPicker1.Year = Year(Date) Me.DTPicker1.Month = Month(Date) Me.DTPicker1.Day = Day(Date) HTH, Eric "Shawn" wrote: Sorry needed to correct first post How do I access a Date picker on a Multipage? I want to assign it a value. I have tried frmTMF.MultiPage1.DTPickerReviewDate.Value = Date and frmTMF.DTPickerReviewDate.Value = Date first returns error 438 - Object doesn't support this property or method second returns error 35788 - An error occured in a call to the windows date and time picker control |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Picker on Multipage
If this repliy appears more than once I apologize - the reply button is
having issues Me.DTPicker1.Value = Date does not work with a multipage Me.MultiPage1.Pages(1).DTPicker1.Value = Date returns error 438 - Object doesn't support this property or method "Jacob Skaria" wrote: If the page is activated Me.DTPicker1.Value = Date If not try on change event... Private Sub MultiPage1_Change() If MultiPage1.SelectedItem.Caption = "Page2" Then Me.MultiPage1.Pages(1).DTPicker1.Value = Date End If End Sub If this post helps click Yes --------------- Jacob Skaria "Shawn" wrote: Sorry needed to correct first post How do I access a Date picker on a Multipage? I want to assign it a value. I have tried frmTMF.MultiPage1.DTPickerReviewDate.Value = Date and frmTMF.DTPickerReviewDate.Value = Date first returns error 438 - Object doesn't support this property or method second returns error 35788 - An error occured in a call to the windows date and time picker control |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Picker on Multipage
Please read the comments along with the code...
If the page/control is *** activated *** and visible on screen Me.DTPicker1.Value = Date 'If not try on ***change event ****... Private Sub MultiPage1_Change() If MultiPage1.SelectedItem.Caption = "Page1" Then Me.DTPicker1.Value = Date + 5 End If End Sub If this post helps click Yes --------------- Jacob Skaria "Shawn" wrote: If this repliy appears more than once I apologize - the reply button is having issues Me.DTPicker1.Value = Date does not work with a multipage Me.MultiPage1.Pages(1).DTPicker1.Value = Date returns error 438 - Object doesn't support this property or method "Jacob Skaria" wrote: If the page is activated Me.DTPicker1.Value = Date If not try on change event... Private Sub MultiPage1_Change() If MultiPage1.SelectedItem.Caption = "Page2" Then Me.MultiPage1.Pages(1).DTPicker1.Value = Date End If End Sub If this post helps click Yes --------------- Jacob Skaria "Shawn" wrote: Sorry needed to correct first post How do I access a Date picker on a Multipage? I want to assign it a value. I have tried frmTMF.MultiPage1.DTPickerReviewDate.Value = Date and frmTMF.DTPickerReviewDate.Value = Date first returns error 438 - Object doesn't support this property or method second returns error 35788 - An error occured in a call to the windows date and time picker control |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Picker on Multipage
Actually, I placed the DTP on the second page of a multi-page control on a
user form, and the code I supplied you worked perfectly. Not sure why yours won't work, but mine did. It also works this way: Me.MultiPage1.Pages("Page2").DTPicker1.Year = 2009 Me.MultiPage1.Pages("Page2").DTPicker1.Month = 7 Me.MultiPage1.Pages("Page2").DTPicker1.Day = 1 Eric "Shawn" wrote: This does not work on a multipage on a userform - sorry if I was not clear on that. A userform with a multipage that has a date picker in it. "EricG" wrote: This worked for me: Me.DTPicker1.Year = Year(Date) Me.DTPicker1.Month = Month(Date) Me.DTPicker1.Day = Day(Date) HTH, Eric "Shawn" wrote: Sorry needed to correct first post How do I access a Date picker on a Multipage? I want to assign it a value. I have tried frmTMF.MultiPage1.DTPickerReviewDate.Value = Date and frmTMF.DTPickerReviewDate.Value = Date first returns error 438 - Object doesn't support this property or method second returns error 35788 - An error occured in a call to the windows date and time picker control |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Picker on Multipage
Select Page1 and then try running your code. It appears the page the control
is on must be active in order to address the control's properties. -- Rick (MVP - Excel) "EricG" wrote in message ... Actually, I placed the DTP on the second page of a multi-page control on a user form, and the code I supplied you worked perfectly. Not sure why yours won't work, but mine did. It also works this way: Me.MultiPage1.Pages("Page2").DTPicker1.Year = 2009 Me.MultiPage1.Pages("Page2").DTPicker1.Month = 7 Me.MultiPage1.Pages("Page2").DTPicker1.Day = 1 Eric "Shawn" wrote: This does not work on a multipage on a userform - sorry if I was not clear on that. A userform with a multipage that has a date picker in it. "EricG" wrote: This worked for me: Me.DTPicker1.Year = Year(Date) Me.DTPicker1.Month = Month(Date) Me.DTPicker1.Day = Day(Date) HTH, Eric "Shawn" wrote: Sorry needed to correct first post How do I access a Date picker on a Multipage? I want to assign it a value. I have tried frmTMF.MultiPage1.DTPickerReviewDate.Value = Date and frmTMF.DTPickerReviewDate.Value = Date first returns error 438 - Object doesn't support this property or method second returns error 35788 - An error occured in a call to the windows date and time picker control |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Picker on Multipage
Try this code...
Dim CurrentPage As Long ..... ..... With Me.MultiPage1 CurrentPage = .Value .Value = 1 ' This is tab number (zero based) containing the DatePicker DTPickerReviewDate.Value = Date .Value = CurrentPage End With -- Rick (MVP - Excel) "Shawn" wrote in message ... How do I access a Date picker on a Multipage? I want to assign it a value. I have tried frmTMF.MultiPage1.Pages.Item(DTPickerReviewDate).V alue = Date and frmTMF.DTPickerReviewDate.Value = Date both return error 438 - Object doesn't support this property or method |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Picker on Multipage
Thank you very much for your help! I was able to use what you all gave me to
finally get an answer. I failed to mention was that it was a DTPicker in a multipage in a multipage ;( - hence nothing worked like it should have. So using what you all had said I got it to work with this Dim ctrl As msforms.Control Dim col As Integer ' this is set elsewhere Dim dDate As String For Each ctrl In .Controls If TypeName(ctrl) = "DTPicker" Then With ctrl Select Case (ctrl.Name) Case ("DTPickerDate") dDate = Cells(53, col).Value frm.MultiPage1.Value = 3 frm.MultiPage2.Value = 0 If frm.MultiPage2.Pages("Page1").Visible = True Then .Value = dDate End If Case ("DTPickerTestDate") dDate = Cells(61, col).Value frm.MultiPage1.Value = 3 frm.MultiPage2.Value = 1 If frm.MultiPage2.Pages("Page2").Visible = True Then .Value = dDate End If Case Else End Select End With End If Next End With Thank you again - maybe this will help someone else aswell. "Shawn" wrote: Sorry needed to correct first post How do I access a Date picker on a Multipage? I want to assign it a value. I have tried frmTMF.MultiPage1.DTPickerReviewDate.Value = Date and frmTMF.DTPickerReviewDate.Value = Date first returns error 438 - Object doesn't support this property or method second returns error 35788 - An error occured in a call to the windows date and time picker control |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Picker on Multipage
I'm not sure you understood the concept behind the code I posted in my
response. You do not need to have the DatePicker control visibly visible in order to set it. Here is what I had posted (minus the variable declaration)... With Me.MultiPage1 CurrentPage = .Value .Value = 1 ' This is tab number (zero based) containing the DatePicker DTPickerReviewDate.Value = Date .Value = CurrentPage End With Let's say that Tab #0 (default name Page1) is visible... the first line of code stores this in the CurrentPage variable. Now, even though the second line of code changes the Value property to 1, which makes Tab #1 (default name Page2) visible, VB will not show you that until the subroutine finishes running... however, BEFORE the subroutine finishes, the code sets the Value property back to the originally visible tab. The net result of this is the display does NOT visibly change to the user... he/she sees a static looking UserForm during the execution of the above code; but for the code's purposes, the tab was made "visible" long enough so that the DatePicker's value could be changed. Now, I see from your code that you have (at least) two MultiPage controls. Believe it or not, the above trick can be played with the visibility of the MultiPage control (housing the two DatePicker controls) as well... you do not have to test the visibility as your posted code does. Okay, now to your code. You do not have to iterate each control on the UserForm looking for your two DatePicker controls... you can address them directly. Also, using the above discussion, you do not have to test for visibility either. Okay, let's see if we can implement all of this and apply it to your code. I believe you can *replace* the entire For..Next block of code that you posted with this code (declare the CurrentPage variable as Long and the MPVisibleState variable as Boolean)... frm.MultiPage1.Value = 3 With frm.MultiPage2 MPVisibleState = .Visible .Visible = True CurrentPage = .Value .Value = 0 DTPickerDate.Value = Cells(53, col).Value .Value = 1 DTPickerTestDate.Value = Cells(61, col).Value .Value = CurrentPage .Visible = MPVisibleState End With I believe this code will run that same as what you posted and that the user should not see any of the visibility manipulations that are taking place. -- Rick (MVP - Excel) "Shawn" wrote in message ... Thank you very much for your help! I was able to use what you all gave me to finally get an answer. I failed to mention was that it was a DTPicker in a multipage in a multipage ;( - hence nothing worked like it should have. So using what you all had said I got it to work with this Dim ctrl As msforms.Control Dim col As Integer ' this is set elsewhere Dim dDate As String For Each ctrl In .Controls If TypeName(ctrl) = "DTPicker" Then With ctrl Select Case (ctrl.Name) Case ("DTPickerDate") dDate = Cells(53, col).Value frm.MultiPage1.Value = 3 frm.MultiPage2.Value = 0 If frm.MultiPage2.Pages("Page1").Visible = True Then .Value = dDate End If Case ("DTPickerTestDate") dDate = Cells(61, col).Value frm.MultiPage1.Value = 3 frm.MultiPage2.Value = 1 If frm.MultiPage2.Pages("Page2").Visible = True Then .Value = dDate End If Case Else End Select End With End If Next End With Thank you again - maybe this will help someone else aswell. "Shawn" wrote: Sorry needed to correct first post How do I access a Date picker on a Multipage? I want to assign it a value. I have tried frmTMF.MultiPage1.DTPickerReviewDate.Value = Date and frmTMF.DTPickerReviewDate.Value = Date first returns error 438 - Object doesn't support this property or method second returns error 35788 - An error occured in a call to the windows date and time picker control |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Picker on Multipage
Thank you so much for your assistance with this.
I have tried what you said but when actually trying to set the DTPICKER.value it gives Run Time Error 424 - Object required It did not occur to me before that this code is in a module not in the frm code. "Rick Rothstein" wrote: I'm not sure you understood the concept behind the code I posted in my response. You do not need to have the DatePicker control visibly visible in order to set it. Here is what I had posted (minus the variable declaration)... With Me.MultiPage1 CurrentPage = .Value .Value = 1 ' This is tab number (zero based) containing the DatePicker DTPickerReviewDate.Value = Date .Value = CurrentPage End With Let's say that Tab #0 (default name Page1) is visible... the first line of code stores this in the CurrentPage variable. Now, even though the second line of code changes the Value property to 1, which makes Tab #1 (default name Page2) visible, VB will not show you that until the subroutine finishes running... however, BEFORE the subroutine finishes, the code sets the Value property back to the originally visible tab. The net result of this is the display does NOT visibly change to the user... he/she sees a static looking UserForm during the execution of the above code; but for the code's purposes, the tab was made "visible" long enough so that the DatePicker's value could be changed. Now, I see from your code that you have (at least) two MultiPage controls. Believe it or not, the above trick can be played with the visibility of the MultiPage control (housing the two DatePicker controls) as well... you do not have to test the visibility as your posted code does. Okay, now to your code. You do not have to iterate each control on the UserForm looking for your two DatePicker controls... you can address them directly. Also, using the above discussion, you do not have to test for visibility either. Okay, let's see if we can implement all of this and apply it to your code. I believe you can *replace* the entire For..Next block of code that you posted with this code (declare the CurrentPage variable as Long and the MPVisibleState variable as Boolean)... frm.MultiPage1.Value = 3 With frm.MultiPage2 MPVisibleState = .Visible .Visible = True CurrentPage = .Value .Value = 0 DTPickerDate.Value = Cells(53, col).Value .Value = 1 DTPickerTestDate.Value = Cells(61, col).Value .Value = CurrentPage .Visible = MPVisibleState End With I believe this code will run that same as what you posted and that the user should not see any of the visibility manipulations that are taking place. -- Rick (MVP - Excel) "Shawn" wrote in message ... Thank you very much for your help! I was able to use what you all gave me to finally get an answer. I failed to mention was that it was a DTPicker in a multipage in a multipage ;( - hence nothing worked like it should have. So using what you all had said I got it to work with this Dim ctrl As msforms.Control Dim col As Integer ' this is set elsewhere Dim dDate As String For Each ctrl In .Controls If TypeName(ctrl) = "DTPicker" Then With ctrl Select Case (ctrl.Name) Case ("DTPickerDate") dDate = Cells(53, col).Value frm.MultiPage1.Value = 3 frm.MultiPage2.Value = 0 If frm.MultiPage2.Pages("Page1").Visible = True Then .Value = dDate End If Case ("DTPickerTestDate") dDate = Cells(61, col).Value frm.MultiPage1.Value = 3 frm.MultiPage2.Value = 1 If frm.MultiPage2.Pages("Page2").Visible = True Then .Value = dDate End If Case Else End Select End With End If Next End With Thank you again - maybe this will help someone else aswell. "Shawn" wrote: Sorry needed to correct first post How do I access a Date picker on a Multipage? I want to assign it a value. I have tried frmTMF.MultiPage1.DTPickerReviewDate.Value = Date and frmTMF.DTPickerReviewDate.Value = Date first returns error 438 - Object doesn't support this property or method second returns error 35788 - An error occured in a call to the windows date and time picker control |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Picker on Multipage
I'm unclear whether you are still having a problem with my coding concept or
whether your last sentence was meant to say you have solve any problems you were having with it. However, if you are not running your code from the UserForm (where I tested my concept out from), then perhaps you need to qualify the DatePicker controls as to where they are located. Maybe this... frm.MultiPage1.Value = 3 With frm.MultiPage2 MPVisibleState = .Visible .Visible = True CurrentPage = .Value .Value = 0 frm.DTPickerDate.Value = Cells(53, col).Value .Value = 1 frm.DTPickerTestDate.Value = Cells(61, col).Value .Value = CurrentPage .Visible = MPVisibleState End With If you are still having trouble with implementing my code concept, you can send me your workbook (remove the NO.SPAM stuff from my email address) and I'll be happy to look at it to see if I can implement it for you. -- Rick (MVP - Excel) "Shawn" wrote in message ... Thank you so much for your assistance with this. I have tried what you said but when actually trying to set the DTPICKER.value it gives Run Time Error 424 - Object required It did not occur to me before that this code is in a module not in the frm code. "Rick Rothstein" wrote: I'm not sure you understood the concept behind the code I posted in my response. You do not need to have the DatePicker control visibly visible in order to set it. Here is what I had posted (minus the variable declaration)... With Me.MultiPage1 CurrentPage = .Value .Value = 1 ' This is tab number (zero based) containing the DatePicker DTPickerReviewDate.Value = Date .Value = CurrentPage End With Let's say that Tab #0 (default name Page1) is visible... the first line of code stores this in the CurrentPage variable. Now, even though the second line of code changes the Value property to 1, which makes Tab #1 (default name Page2) visible, VB will not show you that until the subroutine finishes running... however, BEFORE the subroutine finishes, the code sets the Value property back to the originally visible tab. The net result of this is the display does NOT visibly change to the user... he/she sees a static looking UserForm during the execution of the above code; but for the code's purposes, the tab was made "visible" long enough so that the DatePicker's value could be changed. Now, I see from your code that you have (at least) two MultiPage controls. Believe it or not, the above trick can be played with the visibility of the MultiPage control (housing the two DatePicker controls) as well... you do not have to test the visibility as your posted code does. Okay, now to your code. You do not have to iterate each control on the UserForm looking for your two DatePicker controls... you can address them directly. Also, using the above discussion, you do not have to test for visibility either. Okay, let's see if we can implement all of this and apply it to your code. I believe you can *replace* the entire For..Next block of code that you posted with this code (declare the CurrentPage variable as Long and the MPVisibleState variable as Boolean)... frm.MultiPage1.Value = 3 With frm.MultiPage2 MPVisibleState = .Visible .Visible = True CurrentPage = .Value .Value = 0 DTPickerDate.Value = Cells(53, col).Value .Value = 1 DTPickerTestDate.Value = Cells(61, col).Value .Value = CurrentPage .Visible = MPVisibleState End With I believe this code will run that same as what you posted and that the user should not see any of the visibility manipulations that are taking place. -- Rick (MVP - Excel) "Shawn" wrote in message ... Thank you very much for your help! I was able to use what you all gave me to finally get an answer. I failed to mention was that it was a DTPicker in a multipage in a multipage ;( - hence nothing worked like it should have. So using what you all had said I got it to work with this Dim ctrl As msforms.Control Dim col As Integer ' this is set elsewhere Dim dDate As String For Each ctrl In .Controls If TypeName(ctrl) = "DTPicker" Then With ctrl Select Case (ctrl.Name) Case ("DTPickerDate") dDate = Cells(53, col).Value frm.MultiPage1.Value = 3 frm.MultiPage2.Value = 0 If frm.MultiPage2.Pages("Page1").Visible = True Then .Value = dDate End If Case ("DTPickerTestDate") dDate = Cells(61, col).Value frm.MultiPage1.Value = 3 frm.MultiPage2.Value = 1 If frm.MultiPage2.Pages("Page2").Visible = True Then .Value = dDate End If Case Else End Select End With End If Next End With Thank you again - maybe this will help someone else aswell. "Shawn" wrote: Sorry needed to correct first post How do I access a Date picker on a Multipage? I want to assign it a value. I have tried frmTMF.MultiPage1.DTPickerReviewDate.Value = Date and frmTMF.DTPickerReviewDate.Value = Date first returns error 438 - Object doesn't support this property or method second returns error 35788 - An error occured in a call to the windows date and time picker control |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Picker | Excel Discussion (Misc queries) | |||
Date Picker | Excel Discussion (Misc queries) | |||
Date Picker | Excel Programming | |||
Cant set the date into a Date & Time Picker from Cell value | Excel Programming | |||
date picker | Excel Programming |