Macro to modify property of an object

[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

 

[Harald Staff]

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]

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 Staff]

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]

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]

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.

 

[exceluser]

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

 

[Harald Staff]

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

Is the use of different modules merely for organizational
purposes ?

Yes. (There's a limit for how many characters a module can contain, but you
won't reach that limit)

Best wishes Harald

 

[exceluser]

Harald,

Thanks again for the info.




Exceluser