I am building a production schedule UserForm, which has 15 departments, and
by each department label I included a DTPicker Control. This way my users
can select the due of each department for a particular product. My question
is this, is there a way to clear the out the date in the drop down box so no
date shows? I have cleared the date under the Value Property box, but when I
intialize the UserForm the DTPicker Control displays todays date, I guess by
default? If you have any other ways around this I would greatly appreciate
it, because I think the UserForm looks to busy, with all the dates displayed
even though the CheckBox = False.

Note: I'm using Excel 2003, Microsoft Date & Time Picker Control 6.0 (SP4)

Additonally,

I set the DTPicker Control CheckBox Property to True (so a checkbox is
displayed next to the date in the drop down box). I also have two textboxes
next to each DTPicker Control. I would like to know if there is a way set
the Visible Property of the two textboxes to False next to the Picker Control
if the Picker Controls Checkbox is not checked. For example:

Private Sub dtpEngineer_Click()

dtpEngineer.CheckBox.Value = TextBox1.Visible
dtpEngineer.CheckBox.Value = TextBox2.Visible

End Sub

That means you have 15 DTPicker Controls, right? Okay, you will have to set some event procedures up individually for each of the 15 DTPicker Controls you have. First, here is the common routines (that is, you only need one of each of these)...

' ***** Start Common Procedures *****
Private Sub FormatDTPicker(PickerControl As DTPicker)
With PickerControl
If .Value = vbNull Then
.Format = dtpCustom
.CustomFormat = "X"
Else
.Format = dtpShortDate
End If
End With
End Sub

Private Sub UserForm_Initialize()
Dim Ctrl As Control
For Each Ctrl In Me.Controls
If TypeName(Ctrl) = "DTPicker" Then
Ctrl.Value = vbNull
FormatDTPicker Ctrl
End If
Next
End Sub
' ***** End Common Procedures *****

Next, you need one of each of the following procedures for **each** DTPicker Control that you have **AND** you have to change the control name references inside each of these procedures to match the actual control's name. Here are the procedures you need for DTPicker1....

' ***** Start Procedures For DTPicker1 Control *****
Private Sub DTPicker1_CloseUp()
FormatDTPicker DTPicker1
End Sub

Private Sub DTPicker1_Format(ByVal CallbackField As String, _
FormattedString As String)
If CallbackField = "X" Then
FormattedString = ""
End If
End Sub

Private Sub DTPicker1_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, _
ByVal X As stdole.OLE_XPOS_PIXELS, _
ByVal Y As stdole.OLE_YPOS_PIXELS)
With DTPicker1
If .Value = vbNull Then
.Value = Now
End If
End With
End Sub
' ***** End Procedures For DTPicker1 Control *****

Now, you have to duplicate the above 3 sets of event procedures for each DTPicker Control you have. After you have done that, the DTPicker Controls will all be blank when the UserForm is first loaded. Afterwards, if you want to blank any single DTPicker Control, just set the Value property of that DatePicker Control to vbNull and then call the FormatDTPicker subroutine passing in the name (not a text string of the name, but the name itself). So, for example, if you wanted to blank out just the DTPicker5 control, you would execute these two lines of code...

DTPicker5.Value = vbNull
FormatDTPicker DTPicker5

The above is adapted from code I developed and have posted in the compiled VB newsgroups over the years. This is easier to do in compiled VB because one can bundle controls into something called a Control Array; all control in an Control Array share the same event procedures and, hence, all of the above duplications you have to do for each DTPicker Control above is avoided in compiled VB. However, I did try out what I have posted for you to do on a limited set of DTPicker Controls situated on a UserForm and the code does work.

Rick


"RyanH" <(E-Mail Removed)> wrote in message news:9E3F2DC6-2EE6-417D-B96D-(E-Mail Removed)...
>I am building a production schedule UserForm, which has 15 departments, and
> by each department label I included a DTPicker Control. This way my users
> can select the due of each department for a particular product. My question
> is this, is there a way to clear the out the date in the drop down box so no
> date shows? I have cleared the date under the Value Property box, but when I
> intialize the UserForm the DTPicker Control displays todays date, I guess by
> default? If you have any other ways around this I would greatly appreciate
> it, because I think the UserForm looks to busy, with all the dates displayed
> even though the CheckBox = False.
>
> Note: I'm using Excel 2003, Microsoft Date & Time Picker Control 6.0 (SP4)
>
> Additonally,
>
> I set the DTPicker Control CheckBox Property to True (so a checkbox is
> displayed next to the date in the drop down box). I also have two textboxes
> next to each DTPicker Control. I would like to know if there is a way set
> the Visible Property of the two textboxes to False next to the Picker Control
> if the Picker Controls Checkbox is not checked. For example:
>
> Private Sub dtpEngineer_Click()
>
> dtpEngineer.CheckBox.Value = TextBox1.Visible
> dtpEngineer.CheckBox.Value = TextBox2.Visible
>
> End Sub

Thanks for your insights. I never knew you could blank out a Date & Time
picker... While I have no immediate need for that little bit of coding genius
I will keep it in mind for next time... Very handy for keeping the UI clean...
--
HTH...

Jim Thomlinson


"Rick Rothstein (MVP - VB)" wrote:

> That means you have 15 DTPicker Controls, right? Okay, you will have to set some event procedures up individually for each of the 15 DTPicker Controls you have. First, here is the common routines (that is, you only need one of each of these)...
>
> ' ***** Start Common Procedures *****
> Private Sub FormatDTPicker(PickerControl As DTPicker)
> With PickerControl
> If .Value = vbNull Then
> .Format = dtpCustom
> .CustomFormat = "X"
> Else
> .Format = dtpShortDate
> End If
> End With
> End Sub
>
> Private Sub UserForm_Initialize()
> Dim Ctrl As Control
> For Each Ctrl In Me.Controls
> If TypeName(Ctrl) = "DTPicker" Then
> Ctrl.Value = vbNull
> FormatDTPicker Ctrl
> End If
> Next
> End Sub
> ' ***** End Common Procedures *****
>
> Next, you need one of each of the following procedures for **each** DTPicker Control that you have **AND** you have to change the control name references inside each of these procedures to match the actual control's name. Here are the procedures you need for DTPicker1....
>
> ' ***** Start Procedures For DTPicker1 Control *****
> Private Sub DTPicker1_CloseUp()
> FormatDTPicker DTPicker1
> End Sub
>
> Private Sub DTPicker1_Format(ByVal CallbackField As String, _
> FormattedString As String)
> If CallbackField = "X" Then
> FormattedString = ""
> End If
> End Sub
>
> Private Sub DTPicker1_MouseDown(ByVal Button As Integer, _
> ByVal Shift As Integer, _
> ByVal X As stdole.OLE_XPOS_PIXELS, _
> ByVal Y As stdole.OLE_YPOS_PIXELS)
> With DTPicker1
> If .Value = vbNull Then
> .Value = Now
> End If
> End With
> End Sub
> ' ***** End Procedures For DTPicker1 Control *****
>
> Now, you have to duplicate the above 3 sets of event procedures for each DTPicker Control you have. After you have done that, the DTPicker Controls will all be blank when the UserForm is first loaded. Afterwards, if you want to blank any single DTPicker Control, just set the Value property of that DatePicker Control to vbNull and then call the FormatDTPicker subroutine passing in the name (not a text string of the name, but the name itself). So, for example, if you wanted to blank out just the DTPicker5 control, you would execute these two lines of code...
>
> DTPicker5.Value = vbNull
> FormatDTPicker DTPicker5
>
> The above is adapted from code I developed and have posted in the compiled VB newsgroups over the years. This is easier to do in compiled VB because one can bundle controls into something called a Control Array; all control in an Control Array share the same event procedures and, hence, all of the above duplications you have to do for each DTPicker Control above is avoided in compiled VB. However, I did try out what I have posted for you to do on a limited set of DTPicker Controls situated on a UserForm and the code does work.
>
> Rick
>
>
> "RyanH" <(E-Mail Removed)> wrote in message news:9E3F2DC6-2EE6-417D-B96D-(E-Mail Removed)...
> >I am building a production schedule UserForm, which has 15 departments, and
> > by each department label I included a DTPicker Control. This way my users
> > can select the due of each department for a particular product. My question
> > is this, is there a way to clear the out the date in the drop down box so no
> > date shows? I have cleared the date under the Value Property box, but when I
> > intialize the UserForm the DTPicker Control displays todays date, I guess by
> > default? If you have any other ways around this I would greatly appreciate
> > it, because I think the UserForm looks to busy, with all the dates displayed
> > even though the CheckBox = False.
> >
> > Note: I'm using Excel 2003, Microsoft Date & Time Picker Control 6.0 (SP4)
> >
> > Additonally,
> >
> > I set the DTPicker Control CheckBox Property to True (so a checkbox is
> > displayed next to the date in the drop down box). I also have two textboxes
> > next to each DTPicker Control. I would like to know if there is a way set
> > the Visible Property of the two textboxes to False next to the Picker Control
> > if the Picker Controls Checkbox is not checked. For example:
> >
> > Private Sub dtpEngineer_Click()
> >
> > dtpEngineer.CheckBox.Value = TextBox1.Visible
> > dtpEngineer.CheckBox.Value = TextBox2.Visible
> >
> > End Sub
>

And thank you for your compliment. I remember well the aggravation and hair pulling (not much of that to spare since I am bald<g>) that I went through while trying to figure out how to make that control work the way I wanted it to. The documentation was not as obvious about it as I would have liked.

Rick


"Jim Thomlinson" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message news:EF5C47E2-D855-44E7-81FB-(E-Mail Removed)...
> Thanks for your insights. I never knew you could blank out a Date & Time
> picker... While I have no immediate need for that little bit of coding genius
> I will keep it in mind for next time... Very handy for keeping the UI clean...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Rick Rothstein (MVP - VB)" wrote:
>
>> That means you have 15 DTPicker Controls, right? Okay, you will have to set some event procedures up individually for each of the 15 DTPicker Controls you have. First, here is the common routines (that is, you only need one of each of these)...
>>
>> ' ***** Start Common Procedures *****
>> Private Sub FormatDTPicker(PickerControl As DTPicker)
>> With PickerControl
>> If .Value = vbNull Then
>> .Format = dtpCustom
>> .CustomFormat = "X"
>> Else
>> .Format = dtpShortDate
>> End If
>> End With
>> End Sub
>>
>> Private Sub UserForm_Initialize()
>> Dim Ctrl As Control
>> For Each Ctrl In Me.Controls
>> If TypeName(Ctrl) = "DTPicker" Then
>> Ctrl.Value = vbNull
>> FormatDTPicker Ctrl
>> End If
>> Next
>> End Sub
>> ' ***** End Common Procedures *****
>>
>> Next, you need one of each of the following procedures for **each** DTPicker Control that you have **AND** you have to change the control name references inside each of these procedures to match the actual control's name. Here are the procedures you need for DTPicker1....
>>
>> ' ***** Start Procedures For DTPicker1 Control *****
>> Private Sub DTPicker1_CloseUp()
>> FormatDTPicker DTPicker1
>> End Sub
>>
>> Private Sub DTPicker1_Format(ByVal CallbackField As String, _
>> FormattedString As String)
>> If CallbackField = "X" Then
>> FormattedString = ""
>> End If
>> End Sub
>>
>> Private Sub DTPicker1_MouseDown(ByVal Button As Integer, _
>> ByVal Shift As Integer, _
>> ByVal X As stdole.OLE_XPOS_PIXELS, _
>> ByVal Y As stdole.OLE_YPOS_PIXELS)
>> With DTPicker1
>> If .Value = vbNull Then
>> .Value = Now
>> End If
>> End With
>> End Sub
>> ' ***** End Procedures For DTPicker1 Control *****
>>
>> Now, you have to duplicate the above 3 sets of event procedures for each DTPicker Control you have. After you have done that, the DTPicker Controls will all be blank when the UserForm is first loaded. Afterwards, if you want to blank any single DTPicker Control, just set the Value property of that DatePicker Control to vbNull and then call the FormatDTPicker subroutine passing in the name (not a text string of the name, but the name itself). So, for example, if you wanted to blank out just the DTPicker5 control, you would execute these two lines of code...
>>
>> DTPicker5.Value = vbNull
>> FormatDTPicker DTPicker5
>>
>> The above is adapted from code I developed and have posted in the compiled VB newsgroups over the years. This is easier to do in compiled VB because one can bundle controls into something called a Control Array; all control in an Control Array share the same event procedures and, hence, all of the above duplications you have to do for each DTPicker Control above is avoided in compiled VB. However, I did try out what I have posted for you to do on a limited set of DTPicker Controls situated on a UserForm and the code does work.
>>
>> Rick
>>
>>
>> "RyanH" <(E-Mail Removed)> wrote in message news:9E3F2DC6-2EE6-417D-B96D-(E-Mail Removed)...
>> >I am building a production schedule UserForm, which has 15 departments, and
>> > by each department label I included a DTPicker Control. This way my users
>> > can select the due of each department for a particular product. My question
>> > is this, is there a way to clear the out the date in the drop down box so no
>> > date shows? I have cleared the date under the Value Property box, but when I
>> > intialize the UserForm the DTPicker Control displays todays date, I guess by
>> > default? If you have any other ways around this I would greatly appreciate
>> > it, because I think the UserForm looks to busy, with all the dates displayed
>> > even though the CheckBox = False.
>> >
>> > Note: I'm using Excel 2003, Microsoft Date & Time Picker Control 6.0 (SP4)
>> >
>> > Additonally,
>> >
>> > I set the DTPicker Control CheckBox Property to True (so a checkbox is
>> > displayed next to the date in the drop down box). I also have two textboxes
>> > next to each DTPicker Control. I would like to know if there is a way set
>> > the Visible Property of the two textboxes to False next to the Picker Control
>> > if the Picker Controls Checkbox is not checked. For example:
>> >
>> > Private Sub dtpEngineer_Click()
>> >
>> > dtpEngineer.CheckBox.Value = TextBox1.Visible
>> > dtpEngineer.CheckBox.Value = TextBox2.Visible
>> >
>> > End Sub
>>

I'm not bald yet but I know the one time I tried I grew a little more
forehead that day...
--
HTH...

Jim Thomlinson


"Rick Rothstein (MVP - VB)" wrote:

> And thank you for your compliment. I remember well the aggravation and hair pulling (not much of that to spare since I am bald<g>) that I went through while trying to figure out how to make that control work the way I wanted it to. The documentation was not as obvious about it as I would have liked.
>
> Rick
>
>
> "Jim Thomlinson" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message news:EF5C47E2-D855-44E7-81FB-(E-Mail Removed)...
> > Thanks for your insights. I never knew you could blank out a Date & Time
> > picker... While I have no immediate need for that little bit of coding genius
> > I will keep it in mind for next time... Very handy for keeping the UI clean...
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Rick Rothstein (MVP - VB)" wrote:
> >
> >> That means you have 15 DTPicker Controls, right? Okay, you will have to set some event procedures up individually for each of the 15 DTPicker Controls you have. First, here is the common routines (that is, you only need one of each of these)...
> >>
> >> ' ***** Start Common Procedures *****
> >> Private Sub FormatDTPicker(PickerControl As DTPicker)
> >> With PickerControl
> >> If .Value = vbNull Then
> >> .Format = dtpCustom
> >> .CustomFormat = "X"
> >> Else
> >> .Format = dtpShortDate
> >> End If
> >> End With
> >> End Sub
> >>
> >> Private Sub UserForm_Initialize()
> >> Dim Ctrl As Control
> >> For Each Ctrl In Me.Controls
> >> If TypeName(Ctrl) = "DTPicker" Then
> >> Ctrl.Value = vbNull
> >> FormatDTPicker Ctrl
> >> End If
> >> Next
> >> End Sub
> >> ' ***** End Common Procedures *****
> >>
> >> Next, you need one of each of the following procedures for **each** DTPicker Control that you have **AND** you have to change the control name references inside each of these procedures to match the actual control's name. Here are the procedures you need for DTPicker1....
> >>
> >> ' ***** Start Procedures For DTPicker1 Control *****
> >> Private Sub DTPicker1_CloseUp()
> >> FormatDTPicker DTPicker1
> >> End Sub
> >>
> >> Private Sub DTPicker1_Format(ByVal CallbackField As String, _
> >> FormattedString As String)
> >> If CallbackField = "X" Then
> >> FormattedString = ""
> >> End If
> >> End Sub
> >>
> >> Private Sub DTPicker1_MouseDown(ByVal Button As Integer, _
> >> ByVal Shift As Integer, _
> >> ByVal X As stdole.OLE_XPOS_PIXELS, _
> >> ByVal Y As stdole.OLE_YPOS_PIXELS)
> >> With DTPicker1
> >> If .Value = vbNull Then
> >> .Value = Now
> >> End If
> >> End With
> >> End Sub
> >> ' ***** End Procedures For DTPicker1 Control *****
> >>
> >> Now, you have to duplicate the above 3 sets of event procedures for each DTPicker Control you have. After you have done that, the DTPicker Controls will all be blank when the UserForm is first loaded. Afterwards, if you want to blank any single DTPicker Control, just set the Value property of that DatePicker Control to vbNull and then call the FormatDTPicker subroutine passing in the name (not a text string of the name, but the name itself). So, for example, if you wanted to blank out just the DTPicker5 control, you would execute these two lines of code...
> >>
> >> DTPicker5.Value = vbNull
> >> FormatDTPicker DTPicker5
> >>
> >> The above is adapted from code I developed and have posted in the compiled VB newsgroups over the years. This is easier to do in compiled VB because one can bundle controls into something called a Control Array; all control in an Control Array share the same event procedures and, hence, all of the above duplications you have to do for each DTPicker Control above is avoided in compiled VB. However, I did try out what I have posted for you to do on a limited set of DTPicker Controls situated on a UserForm and the code does work.
> >>
> >> Rick
> >>
> >>
> >> "RyanH" <(E-Mail Removed)> wrote in message news:9E3F2DC6-2EE6-417D-B96D-(E-Mail Removed)...
> >> >I am building a production schedule UserForm, which has 15 departments, and
> >> > by each department label I included a DTPicker Control. This way my users
> >> > can select the due of each department for a particular product. My question
> >> > is this, is there a way to clear the out the date in the drop down box so no
> >> > date shows? I have cleared the date under the Value Property box, but when I
> >> > intialize the UserForm the DTPicker Control displays todays date, I guess by
> >> > default? If you have any other ways around this I would greatly appreciate
> >> > it, because I think the UserForm looks to busy, with all the dates displayed
> >> > even though the CheckBox = False.
> >> >
> >> > Note: I'm using Excel 2003, Microsoft Date & Time Picker Control 6.0 (SP4)
> >> >
> >> > Additonally,
> >> >
> >> > I set the DTPicker Control CheckBox Property to True (so a checkbox is
> >> > displayed next to the date in the drop down box). I also have two textboxes
> >> > next to each DTPicker Control. I would like to know if there is a way set
> >> > the Visible Property of the two textboxes to False next to the Picker Control
> >> > if the Picker Controls Checkbox is not checked. For example:
> >> >
> >> > Private Sub dtpEngineer_Click()
> >> >
> >> > dtpEngineer.CheckBox.Value = TextBox1.Visible
> >> > dtpEngineer.CheckBox.Value = TextBox2.Visible
> >> >
> >> > End Sub
> >>
>

I had a full head of hair before I started in on that project; look at this picture of me taken shortly after I finished it...

https://mvp.support.microsoft.com/pr...Rick.Rothstein

<g>

Rick


"Jim Thomlinson" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message news:33175877-2AAD-482B-BDD4-(E-Mail Removed)...
> I'm not bald yet but I know the one time I tried I grew a little more
> forehead that day...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Rick Rothstein (MVP - VB)" wrote:
>
>> And thank you for your compliment. I remember well the aggravation and hair pulling (not much of that to spare since I am bald<g>) that I went through while trying to figure out how to make that control work the way I wanted it to. The documentation was not as obvious about it as I would have liked.
>>
>> Rick
>>
>>
>> "Jim Thomlinson" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message news:EF5C47E2-D855-44E7-81FB-(E-Mail Removed)...
>> > Thanks for your insights. I never knew you could blank out a Date & Time
>> > picker... While I have no immediate need for that little bit of coding genius
>> > I will keep it in mind for next time... Very handy for keeping the UI clean...
>> > --
>> > HTH...
>> >
>> > Jim Thomlinson
>> >
>> >
>> > "Rick Rothstein (MVP - VB)" wrote:
>> >
>> >> That means you have 15 DTPicker Controls, right? Okay, you will have to set some event procedures up individually for each of the 15 DTPicker Controls you have. First, here is the common routines (that is, you only need one of each of these)...
>> >>
>> >> ' ***** Start Common Procedures *****
>> >> Private Sub FormatDTPicker(PickerControl As DTPicker)
>> >> With PickerControl
>> >> If .Value = vbNull Then
>> >> .Format = dtpCustom
>> >> .CustomFormat = "X"
>> >> Else
>> >> .Format = dtpShortDate
>> >> End If
>> >> End With
>> >> End Sub
>> >>
>> >> Private Sub UserForm_Initialize()
>> >> Dim Ctrl As Control
>> >> For Each Ctrl In Me.Controls
>> >> If TypeName(Ctrl) = "DTPicker" Then
>> >> Ctrl.Value = vbNull
>> >> FormatDTPicker Ctrl
>> >> End If
>> >> Next
>> >> End Sub
>> >> ' ***** End Common Procedures *****
>> >>
>> >> Next, you need one of each of the following procedures for **each** DTPicker Control that you have **AND** you have to change the control name references inside each of these procedures to match the actual control's name. Here are the procedures you need for DTPicker1....
>> >>
>> >> ' ***** Start Procedures For DTPicker1 Control *****
>> >> Private Sub DTPicker1_CloseUp()
>> >> FormatDTPicker DTPicker1
>> >> End Sub
>> >>
>> >> Private Sub DTPicker1_Format(ByVal CallbackField As String, _
>> >> FormattedString As String)
>> >> If CallbackField = "X" Then
>> >> FormattedString = ""
>> >> End If
>> >> End Sub
>> >>
>> >> Private Sub DTPicker1_MouseDown(ByVal Button As Integer, _
>> >> ByVal Shift As Integer, _
>> >> ByVal X As stdole.OLE_XPOS_PIXELS, _
>> >> ByVal Y As stdole.OLE_YPOS_PIXELS)
>> >> With DTPicker1
>> >> If .Value = vbNull Then
>> >> .Value = Now
>> >> End If
>> >> End With
>> >> End Sub
>> >> ' ***** End Procedures For DTPicker1 Control *****
>> >>
>> >> Now, you have to duplicate the above 3 sets of event procedures for each DTPicker Control you have. After you have done that, the DTPicker Controls will all be blank when the UserForm is first loaded. Afterwards, if you want to blank any single DTPicker Control, just set the Value property of that DatePicker Control to vbNull and then call the FormatDTPicker subroutine passing in the name (not a text string of the name, but the name itself). So, for example, if you wanted to blank out just the DTPicker5 control, you would execute these two lines of code...
>> >>
>> >> DTPicker5.Value = vbNull
>> >> FormatDTPicker DTPicker5
>> >>
>> >> The above is adapted from code I developed and have posted in the compiled VB newsgroups over the years. This is easier to do in compiled VB because one can bundle controls into something called a Control Array; all control in an Control Array share the same event procedures and, hence, all of the above duplications you have to do for each DTPicker Control above is avoided in compiled VB. However, I did try out what I have posted for you to do on a limited set of DTPicker Controls situated on a UserForm and the code does work.
>> >>
>> >> Rick
>> >>
>> >>
>> >> "RyanH" <(E-Mail Removed)> wrote in message news:9E3F2DC6-2EE6-417D-B96D-(E-Mail Removed)...
>> >> >I am building a production schedule UserForm, which has 15 departments, and
>> >> > by each department label I included a DTPicker Control. This way my users
>> >> > can select the due of each department for a particular product. My question
>> >> > is this, is there a way to clear the out the date in the drop down box so no
>> >> > date shows? I have cleared the date under the Value Property box, but when I
>> >> > intialize the UserForm the DTPicker Control displays todays date, I guess by
>> >> > default? If you have any other ways around this I would greatly appreciate
>> >> > it, because I think the UserForm looks to busy, with all the dates displayed
>> >> > even though the CheckBox = False.
>> >> >
>> >> > Note: I'm using Excel 2003, Microsoft Date & Time Picker Control 6.0 (SP4)
>> >> >
>> >> > Additonally,
>> >> >
>> >> > I set the DTPicker Control CheckBox Property to True (so a checkbox is
>> >> > displayed next to the date in the drop down box). I also have two textboxes
>> >> > next to each DTPicker Control. I would like to know if there is a way set
>> >> > the Visible Property of the two textboxes to False next to the Picker Control
>> >> > if the Picker Controls Checkbox is not checked. For example:
>> >> >
>> >> > Private Sub dtpEngineer_Click()
>> >> >
>> >> > dtpEngineer.CheckBox.Value = TextBox1.Visible
>> >> > dtpEngineer.CheckBox.Value = TextBox2.Visible
>> >> >
>> >> > End Sub
>> >>
>>

I think that there was a mistake with the picture. I _think_ that's Steve
Ballmer <vbg>.

"Rick Rothstein (MVP - VB)" wrote:
>
> I had a full head of hair before I started in on that project; look at this picture of me taken shortly after I finished it...
>
> https://mvp.support.microsoft.com/pr...Rick.Rothstein
>
> <g>
>
> Rick
>
> "Jim Thomlinson" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message news:33175877-2AAD-482B-BDD4-(E-Mail Removed)...
> > I'm not bald yet but I know the one time I tried I grew a little more
> > forehead that day...
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Rick Rothstein (MVP - VB)" wrote:
> >
> >> And thank you for your compliment. I remember well the aggravation and hair pulling (not much of that to spare since I am bald<g>) that I went through while trying to figure out how to make that control work the way I wanted it to. The documentation was not as obvious about it as I would have liked.
> >>
> >> Rick
> >>
> >>
> >> "Jim Thomlinson" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message news:EF5C47E2-D855-44E7-81FB-(E-Mail Removed)...
> >> > Thanks for your insights. I never knew you could blank out a Date & Time
> >> > picker... While I have no immediate need for that little bit of coding genius
> >> > I will keep it in mind for next time... Very handy for keeping the UI clean...
> >> > --
> >> > HTH...
> >> >
> >> > Jim Thomlinson
> >> >
> >> >
> >> > "Rick Rothstein (MVP - VB)" wrote:
> >> >
> >> >> That means you have 15 DTPicker Controls, right? Okay, you will have to set some event procedures up individually for each of the 15 DTPicker Controls you have. First, here is the common routines (that is, you only need one of each of these)...
> >> >>
> >> >> ' ***** Start Common Procedures *****
> >> >> Private Sub FormatDTPicker(PickerControl As DTPicker)
> >> >> With PickerControl
> >> >> If .Value = vbNull Then
> >> >> .Format = dtpCustom
> >> >> .CustomFormat = "X"
> >> >> Else
> >> >> .Format = dtpShortDate
> >> >> End If
> >> >> End With
> >> >> End Sub
> >> >>
> >> >> Private Sub UserForm_Initialize()
> >> >> Dim Ctrl As Control
> >> >> For Each Ctrl In Me.Controls
> >> >> If TypeName(Ctrl) = "DTPicker" Then
> >> >> Ctrl.Value = vbNull
> >> >> FormatDTPicker Ctrl
> >> >> End If
> >> >> Next
> >> >> End Sub
> >> >> ' ***** End Common Procedures *****
> >> >>
> >> >> Next, you need one of each of the following procedures for **each** DTPicker Control that you have **AND** you have to change the control name references inside each of these procedures to match the actual control's name. Here are the procedures you need for DTPicker1....
> >> >>
> >> >> ' ***** Start Procedures For DTPicker1 Control *****
> >> >> Private Sub DTPicker1_CloseUp()
> >> >> FormatDTPicker DTPicker1
> >> >> End Sub
> >> >>
> >> >> Private Sub DTPicker1_Format(ByVal CallbackField As String, _
> >> >> FormattedString As String)
> >> >> If CallbackField = "X" Then
> >> >> FormattedString = ""
> >> >> End If
> >> >> End Sub
> >> >>
> >> >> Private Sub DTPicker1_MouseDown(ByVal Button As Integer, _
> >> >> ByVal Shift As Integer, _
> >> >> ByVal X As stdole.OLE_XPOS_PIXELS, _
> >> >> ByVal Y As stdole.OLE_YPOS_PIXELS)
> >> >> With DTPicker1
> >> >> If .Value = vbNull Then
> >> >> .Value = Now
> >> >> End If
> >> >> End With
> >> >> End Sub
> >> >> ' ***** End Procedures For DTPicker1 Control *****
> >> >>
> >> >> Now, you have to duplicate the above 3 sets of event procedures for each DTPicker Control you have. After you have done that, the DTPicker Controls will all be blank when the UserForm is first loaded. Afterwards, if you want to blank any single DTPicker Control, just set the Value property of that DatePicker Control to vbNull and then call the FormatDTPicker subroutine passing in the name (not a text string of the name, but the name itself). So, for example, if you wanted to blank out just the DTPicker5 control, you would execute these two lines of code...
> >> >>
> >> >> DTPicker5.Value = vbNull
> >> >> FormatDTPicker DTPicker5
> >> >>
> >> >> The above is adapted from code I developed and have posted in the compiled VB newsgroups over the years. This is easier to do in compiled VB because one can bundle controls into something called a Control Array; all control in an Control Array share the same event procedures and, hence, all of the above duplications you have to do for each DTPicker Control above is avoided in compiled VB. However, I did try out what I have posted for you to do on a limited set of DTPicker Controls situated on a UserForm and the code does work.
> >> >>
> >> >> Rick
> >> >>
> >> >>
> >> >> "RyanH" <(E-Mail Removed)> wrote in message news:9E3F2DC6-2EE6-417D-B96D-(E-Mail Removed)...
> >> >> >I am building a production schedule UserForm, which has 15 departments, and
> >> >> > by each department label I included a DTPicker Control. This way my users
> >> >> > can select the due of each department for a particular product. My question
> >> >> > is this, is there a way to clear the out the date in the drop down box so no
> >> >> > date shows? I have cleared the date under the Value Property box, but when I
> >> >> > intialize the UserForm the DTPicker Control displays todays date, I guess by
> >> >> > default? If you have any other ways around this I would greatly appreciate
> >> >> > it, because I think the UserForm looks to busy, with all the dates displayed
> >> >> > even though the CheckBox = False.
> >> >> >
> >> >> > Note: I'm using Excel 2003, Microsoft Date & Time Picker Control 6.0 (SP4)
> >> >> >
> >> >> > Additonally,
> >> >> >
> >> >> > I set the DTPicker Control CheckBox Property to True (so a checkbox is
> >> >> > displayed next to the date in the drop down box). I also have two textboxes
> >> >> > next to each DTPicker Control. I would like to know if there is a way set
> >> >> > the Visible Property of the two textboxes to False next to the Picker Control
> >> >> > if the Picker Controls Checkbox is not checked. For example:
> >> >> >
> >> >> > Private Sub dtpEngineer_Click()
> >> >> >
> >> >> > dtpEngineer.CheckBox.Value = TextBox1.Visible
> >> >> > dtpEngineer.CheckBox.Value = TextBox2.Visible
> >> >> >
> >> >> > End Sub
> >> >>
> >>

--

Dave Peterson

Come on.... I have **way** more hair than he does.

http://www.microsoft.com/Presspass/e...e/default.mspx

Rick


"Dave Peterson" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
>I think that there was a mistake with the picture. I _think_ that's Steve
> Ballmer <vbg>.
>
> "Rick Rothstein (MVP - VB)" wrote:
>>
>> I had a full head of hair before I started in on that project; look at this picture of me taken shortly after I finished it...
>>
>> https://mvp.support.microsoft.com/pr...Rick.Rothstein
>>
>> <g>
>>
>> Rick
>>
>> "Jim Thomlinson" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message news:33175877-2AAD-482B-BDD4-(E-Mail Removed)...
>> > I'm not bald yet but I know the one time I tried I grew a little more
>> > forehead that day...
>> > --
>> > HTH...
>> >
>> > Jim Thomlinson
>> >
>> >
>> > "Rick Rothstein (MVP - VB)" wrote:
>> >
>> >> And thank you for your compliment. I remember well the aggravation and hair pulling (not much of that to spare since I am bald<g>) that I went through while trying to figure out how to make that control work the way I wanted it to. The documentation was not as obvious about it as I would have liked.
>> >>
>> >> Rick
>> >>
>> >>
>> >> "Jim Thomlinson" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message news:EF5C47E2-D855-44E7-81FB-(E-Mail Removed)...
>> >> > Thanks for your insights. I never knew you could blank out a Date & Time
>> >> > picker... While I have no immediate need for that little bit of coding genius
>> >> > I will keep it in mind for next time... Very handy for keeping the UI clean...
>> >> > --
>> >> > HTH...
>> >> >
>> >> > Jim Thomlinson
>> >> >
>> >> >
>> >> > "Rick Rothstein (MVP - VB)" wrote:
>> >> >
>> >> >> That means you have 15 DTPicker Controls, right? Okay, you will have to set some event procedures up individually for each of the 15 DTPicker Controls you have. First, here is the common routines (that is, you only need one of each of these)...
>> >> >>
>> >> >> ' ***** Start Common Procedures *****
>> >> >> Private Sub FormatDTPicker(PickerControl As DTPicker)
>> >> >> With PickerControl
>> >> >> If .Value = vbNull Then
>> >> >> .Format = dtpCustom
>> >> >> .CustomFormat = "X"
>> >> >> Else
>> >> >> .Format = dtpShortDate
>> >> >> End If
>> >> >> End With
>> >> >> End Sub
>> >> >>
>> >> >> Private Sub UserForm_Initialize()
>> >> >> Dim Ctrl As Control
>> >> >> For Each Ctrl In Me.Controls
>> >> >> If TypeName(Ctrl) = "DTPicker" Then
>> >> >> Ctrl.Value = vbNull
>> >> >> FormatDTPicker Ctrl
>> >> >> End If
>> >> >> Next
>> >> >> End Sub
>> >> >> ' ***** End Common Procedures *****
>> >> >>
>> >> >> Next, you need one of each of the following procedures for **each** DTPicker Control that you have **AND** you have to change the control name references inside each of these procedures to match the actual control's name. Here are the procedures you need for DTPicker1....
>> >> >>
>> >> >> ' ***** Start Procedures For DTPicker1 Control *****
>> >> >> Private Sub DTPicker1_CloseUp()
>> >> >> FormatDTPicker DTPicker1
>> >> >> End Sub
>> >> >>
>> >> >> Private Sub DTPicker1_Format(ByVal CallbackField As String, _
>> >> >> FormattedString As String)
>> >> >> If CallbackField = "X" Then
>> >> >> FormattedString = ""
>> >> >> End If
>> >> >> End Sub
>> >> >>
>> >> >> Private Sub DTPicker1_MouseDown(ByVal Button As Integer, _
>> >> >> ByVal Shift As Integer, _
>> >> >> ByVal X As stdole.OLE_XPOS_PIXELS, _
>> >> >> ByVal Y As stdole.OLE_YPOS_PIXELS)
>> >> >> With DTPicker1
>> >> >> If .Value = vbNull Then
>> >> >> .Value = Now
>> >> >> End If
>> >> >> End With
>> >> >> End Sub
>> >> >> ' ***** End Procedures For DTPicker1 Control *****
>> >> >>
>> >> >> Now, you have to duplicate the above 3 sets of event procedures for each DTPicker Control you have. After you have done that, the DTPicker Controls will all be blank when the UserForm is first loaded. Afterwards, if you want to blank any single DTPicker Control, just set the Value property of that DatePicker Control to vbNull and then call the FormatDTPicker subroutine passing in the name (not a text string of the name, but the name itself). So, for example, if you wanted to blank out just the DTPicker5 control, you would execute these two lines of code...
>> >> >>
>> >> >> DTPicker5.Value = vbNull
>> >> >> FormatDTPicker DTPicker5
>> >> >>
>> >> >> The above is adapted from code I developed and have posted in the compiled VB newsgroups over the years. This is easier to do in compiled VB because one can bundle controls into something called a Control Array; all control in an Control Array share the same event procedures and, hence, all of the above duplications you have to do for each DTPicker Control above is avoided in compiled VB. However, I did try out what I have posted for you to do on a limited set of DTPicker Controls situated on a UserForm and the code does work.
>> >> >>
>> >> >> Rick
>> >> >>
>> >> >>
>> >> >> "RyanH" <(E-Mail Removed)> wrote in message news:9E3F2DC6-2EE6-417D-B96D-(E-Mail Removed)...
>> >> >> >I am building a production schedule UserForm, which has 15 departments, and
>> >> >> > by each department label I included a DTPicker Control. This way my users
>> >> >> > can select the due of each department for a particular product. My question
>> >> >> > is this, is there a way to clear the out the date in the drop down box so no
>> >> >> > date shows? I have cleared the date under the Value Property box, but when I
>> >> >> > intialize the UserForm the DTPicker Control displays todays date, I guess by
>> >> >> > default? If you have any other ways around this I would greatly appreciate
>> >> >> > it, because I think the UserForm looks to busy, with all the dates displayed
>> >> >> > even though the CheckBox = False.
>> >> >> >
>> >> >> > Note: I'm using Excel 2003, Microsoft Date & Time Picker Control 6.0 (SP4)
>> >> >> >
>> >> >> > Additonally,
>> >> >> >
>> >> >> > I set the DTPicker Control CheckBox Property to True (so a checkbox is
>> >> >> > displayed next to the date in the drop down box). I also have two textboxes
>> >> >> > next to each DTPicker Control. I would like to know if there is a way set
>> >> >> > the Visible Property of the two textboxes to False next to the Picker Control
>> >> >> > if the Picker Controls Checkbox is not checked. For example:
>> >> >> >
>> >> >> > Private Sub dtpEngineer_Click()
>> >> >> >
>> >> >> > dtpEngineer.CheckBox.Value = TextBox1.Visible
>> >> >> > dtpEngineer.CheckBox.Value = TextBox2.Visible
>> >> >> >
>> >> >> > End Sub
>> >> >>
>> >>
>
> --
>
> Dave Peterson

I was confused by the size of his wallet!

"Rick Rothstein (MVP - VB)" wrote:
>
> Come on.... I have **way** more hair than he does.
>
> http://www.microsoft.com/Presspass/e...e/default.mspx
>
> Rick
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> >I think that there was a mistake with the picture. I _think_ that's Steve
> > Ballmer <vbg>.
> >
> > "Rick Rothstein (MVP - VB)" wrote:
> >>
> >> I had a full head of hair before I started in on that project; look at this picture of me taken shortly after I finished it...
> >>
> >> https://mvp.support.microsoft.com/pr...Rick.Rothstein
> >>
> >> <g>
> >>
> >> Rick
> >>
<<snipped>>

LOL

Yeah, I'll concede that one to you... his wallet is just a **wee** bit larger than mine.<g>

Rick


"Dave Peterson" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
>I was confused by the size of his wallet!
>
> "Rick Rothstein (MVP - VB)" wrote:
>>
>> Come on.... I have **way** more hair than he does.
>>
>> http://www.microsoft.com/Presspass/e...e/default.mspx
>>
>> Rick
>>
>> "Dave Peterson" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
>> >I think that there was a mistake with the picture. I _think_ that's Steve
>> > Ballmer <vbg>.
>> >
>> > "Rick Rothstein (MVP - VB)" wrote:
>> >>
>> >> I had a full head of hair before I started in on that project; look at this picture of me taken shortly after I finished it...
>> >>
>> >> https://mvp.support.microsoft.com/pr...Rick.Rothstein
>> >>
>> >> <g>
>> >>
>> >> Rick
>> >>
> <<snipped>>