In Excel 2007, how would you write a macro to set the Enabled property
of multiple DTPicker objects ?

The DTPicker object comes from the Microsoft Date and Time Picker
Control 6.0.

http://support.microsoft.com/kb/297381

For example, there are two DTPicker objects, DTPicker1 and DTPicker2.

Macro1 will set their Enabled property to TRUE.

Macro2 will set their Enabled property to FALSE.



Exceluser

Hi

Sub Macro1()
DTPicker1.Enabled = False
DTPicker2.Enabled = False
End Sub

Contols are not part of any useful collection -unless you create one. Text
loops like
Controls("DTPicker" & i).Enabled = False
are shorter to write but I believe they run slower.

Best wishes Harald

"exceluser" skrev i melding
...
In Excel 2007, how would you write a macro to set the Enabled property
of multiple DTPicker objects ?

The DTPicker object comes from the Microsoft Date and Time Picker
Control 6.0.

http://support.microsoft.com/kb/297381

For example, there are two DTPicker objects, DTPicker1 and DTPicker2.

Macro1 will set their Enabled property to TRUE.

Macro2 will set their Enabled property to FALSE.



Exceluser

Harald,

Thanks for the reply.

When the macro runs, an error dialog appears:

Run-time error '424'

Object required

Any idea as to what's wrong ?



Exceluser

You wrote "For example, there are two DTPicker objects, DTPicker1 and
DTPicker2". Are there really or just for example? Where are they located,
and where is your macro located ?

Best wishes Harald


"exceluser" wrote in message
...
Harald,

Thanks for the reply.

When the macro runs, an error dialog appears:

Run-time error '424'

Object required

Any idea as to what's wrong ?



Exceluser

Harald,

That was fast.

Correction - the objects are on Sheet1 and are named DTPicker21 and
DTPicker22 as displayed next to the formula bar.

However, I took that into account when entering the macro.

The macro was created by:

1) Clicking on the Macros button on the Developer ribbon

2) Entering a name for the macro:

DisableDTPicker

3) Clicking on the Create button

4) Pasting in the following between the Sub and End lines

DTPicker21.Enabled = False
DTPicker22.Enabled = False

5) Closing the VB Editor

The macro is in Module2.




Exceluser

You'll want to tell excel what owns those date pickers.

You can use the sheet name (the one the users see on the tab):
Worksheets("Sheet1").DTPicker21.Enabled = False

Or you can use the CodeName of the sheet
Sheet1.DTPicker21.Enabled = False

Sheet1 is what you see in the project explorer.
Sheet1(NameYouSeeInExcel)

The codename is before the sheet name (which is in parentheses). They don't
need to match.

If the users can change the sheet name, I'd recommend using the CodeName. This
can be changed, too. But it's usually beyond the ability of the average user.



On 10/19/2010 16:58, exceluser wrote:
Harald,

That was fast.

Correction - the objects are on Sheet1 and are named DTPicker21 and
DTPicker22 as displayed next to the formula bar.

However, I took that into account when entering the macro.

The macro was created by:

1) Clicking on the Macros button on the Developer ribbon

2) Entering a name for the macro:

DisableDTPicker

3) Clicking on the Create button

4) Pasting in the following between the Sub and End lines

DTPicker21.Enabled = False
DTPicker22.Enabled = False

5) Closing the VB Editor

The macro is in Module2.




Exceluser


--
Dave Peterson