Since you are trying to reference the FormatDTPicker subroutine that is
located on the UserForm, you have to do two things. First, change the
declaration for the FormatDTPicker subroutine from Private to Public so it
can be seen outside of the UserForm (you are executing your code from the
Worksheet's BeforeDoubleClick event, so procedures declared Private on the
UserForm can't be seen there). Second, you have to use
UserForm1.FormatDTPicker instead of just FormatDTPicker in code line where
your error is happening at (you need to point to tell VBA where it can find
the FormatDTPicker subroutine at). The issues we have been talking about are
known as "scope" and deal with where things are located and the access
restrictions that apply to them.
By the way... one On Error Resume Next statement is all you need in a single
procedure... it will remain in effect from the moment the statement is
executed until an On Error Goto 0 statement is issued or until you exit the
procedure, whichever comes first.
Rick
"RyanH" wrote in message
...
I removed the For Each Loop from the Inialize Event like you said and I am
getting an error Sub or Function not defined. I currently use the
BeforeDoubleClick Event to load my UserFrom. There could be a better way
of
doing this but I'm just not sure how since I am new to VBA.
Here is my code and where they are located:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Cancel = True
ActiveSheet.Cells(ActiveCell.Row, "A").Select
With frmSalesSummary
.txbSalesOrder = Cells(ActiveCell.Row, "A")
On Error Resume Next
.cboSalesPerson = Cells(ActiveCell.Row, "B")
On Error Resume Next
.cboEngineer = Cells(ActiveCell.Row, "C")
.txbCustomer = Cells(ActiveCell.Row, "D")
.txbEndUser = Cells(ActiveCell.Row, "E")
.txbQty = Cells(ActiveCell.Row, "F")
.txbDescription1 = Cells(ActiveCell.Row, "G")
.txbDescription2 = Cells(ActiveCell.Row, "H")
.txbComments = Cells(ActiveCell.Row, "I")
On Error Resume Next
.cboShipMethod = Cells(ActiveCell.Row, "J")
If Cells(ActiveCell.Row, "K") = "" Then
.dtpScheduledShip.Value = ""
Else
.dtpScheduledShip.Value = Cells(ActiveCell.Row, "K")
End If
If Cells(ActiveCell.Row, "L") = "" Then
.dtpActualShip.Value = ""
Else
.dtpActualShip.Value = Cells(ActiveCell.Row, "L")
End If
.txbBOM = Cells(ActiveCell.Row, "M")
.txbSalesPrice = Cells(ActiveCell.Row, "N")
.txbTotalEstHrs = Cells(ActiveCell.Row, "O").Text
.txbTotalActHrs = Cells(ActiveCell.Row, "P").Text
'ENGINEERING: Code recognizes font in grey thus activates associated
checkbox, if black font does deactivates.
If IsDate(Cells(ActiveCell.Row, "Q")) = True Then
.dtpEngineer.Value = Cells(ActiveCell.Row, "Q").Text
Else
dtpEngineer.Value = vbNull
FormatDTPicker dtpEngineer <===ERROR ERROR
End If
.txbEngEstHrs = Cells(ActiveCell.Row, "R").Text
.txbEngActHrs = Cells(ActiveCell.Row, "S").Text
If Cells(ActiveCell.Row, "Q").Font.ColorIndex = 15 Then
.dtpEngineer.Enabled = False
.chkEngineering = True
Else
.dtpEngineer.Enabled = True
.chkEngineering = False
End If
*****There are fourteen more departments coded the same way as ENGINEERING
just with different names.
frmSalesSummary.Show
End Sub
At this point I have probably managed to confuse you. Do you have enough
information from me to determine the issue? Thanks agian for your help on
this!!
"Rick Rothstein (MVP - VB)" wrote:
Yes, it is possible to do that. Since you didn't post any code, I can
only guess at how to direct you to incorporate my routine into it, but
let me try (you should try what I am about to suggest to a copy of your
spreadsheet so as not to mess it up in case something goes wrong in how
you follow my instructions). First of all, you will need to set up
everything I posted earlier **except** for the UserForm Initialize event
procedure (we will use what you have as a base for that event). Once you
have added the 3 event procedures for each of your 15 TextBoxes and added
the FormatDTPicker subroutine, then in your UserForm Initialize event,
wherever you now assign the date to a TextBox, you will need to do this
instead... if the cell you are retrieving your date from is a date,
assign it to the Value property for the DTPicker Control that corresponds
to that cell... else, if the cell is blank, execute these two lines
DTPicker5.Value = vbNull
FormatDTPicker DTPicker5
where you would be using the name of the DTPicker Control that
corresponds to that cell in place of the DTPicker5 name I used in these
sample code lines above. Hopefully, that makes sense to you. If you have
any trouble, it would be helpful if you posted your existing DoubleClick
event code and your UserForm Initialize event code for us to look at.
Rick
"RyanH" wrote in message
...
I hope this replys to all. Thanks for getting back to me with that
code.
But I am having an issue with initalizing the UserForm the way I need
it too.
Let me explain my old setup before your code.
My old UserForm had a TextBox next to each department. The user would
double click the cell the Sales Order # was contained in and the
UserForm
would Intialize. When it was Intialized the macro would scan down the
row
that was double clicked and fill the due dates of each department. If
there
wasn't a date the textbox was left blank.
Is there a way to do this with the DTPicker? In other words, when the
UserForm is Initialized if the cell that stores the due date for that
department is blank have the DTPicker Control Value display blank and
when
there is a date have the DTPicker Control Value display that date. Is
this
possible?
Thanks in advanced!!
"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" wrote in message
...
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