Macro to modify property of an object

  • Thread starter Thread starter exceluser
  • Start date Start date
E

exceluser

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
 
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.
 
Dave,

That did it !

Thank you very much.

Is there an issue with having multiple macros in one module ?

For example, there are two macros.

One enables the DTPicker controls and the other disables them.

Is the use of different modules merely for organizational
purposes ?


Exceluser
 
Back
Top