Hi,

On recommendation from a textbook I was reading, I used Forms 1.0 toolbar
buttons to run macros, instead of using CommandButtons from the Controls
toolbar -- a decision I am now regretting. . . Anyway, I am using the
following code to temporarily disable buttons created from the Forms toolbar:

Sheet1.Shapes.Item(strButton).OLEFormat.Object.Ena bled = False

and this does disable the button from calling its OnAction macro. But the
button still appears the same, i.e. the text is not greyed out, and when you
move the mouse over the button, the pointer icon still turns into the "hand".
It is confusing to users, so I would like to make the button's appearance
reflect its current state.

Is there a way to modify the appearance or behavior for this type of button,
other than changing its Visible property to False?

Thank you kindly,

Hi Will,

I also faced a similar problem in one of my projects and I applied the
following idea. Since the button can't be grayed out (or if it can I don't
know), I changed the caption of the macro to something like disabled and
changed the OnAction property to a macro that provides a friendly message.
When I wanted to change the button to an Enabled state from some other code,
I called "CallMyButton" macro with a TRUE switch.

I am providing the code below. Hope that solves your problem to some extent.

Option Explicit

Sub CallMyButton()
EnableMyButton True
End Sub

Sub EnableMyButton(bEnabled As Boolean)
Dim btn As Button
Set btn = Sheets("Sheet1").Buttons("Button 4")
If bEnabled Then
With btn
.Caption = "Enabled"
.OnAction = "HelloWorld"
End With
Else
With btn
.Caption = "Disabled"
.OnAction = "DisableButton"
End With
End If
End Sub

Sub HelloWorld()
MsgBox "Hello World"
End Sub

Sub DisableButton()
MsgBox "Sorry!!! You can't run the macro."
End Sub

--
Anant


"Will Finkle" wrote:

Hi,

On recommendation from a textbook I was reading, I used Forms 1.0 toolbar
buttons to run macros, instead of using CommandButtons from the Controls
toolbar -- a decision I am now regretting. . . Anyway, I am using the
following code to temporarily disable buttons created from the Forms toolbar:

Sheet1.Shapes.Item(strButton).OLEFormat.Object.Ena bled = False

and this does disable the button from calling its OnAction macro. But the
button still appears the same, i.e. the text is not greyed out, and when you
move the mouse over the button, the pointer icon still turns into the "hand".
It is confusing to users, so I would like to make the button's appearance
reflect its current state.

Is there a way to modify the appearance or behavior for this type of button,
other than changing its Visible property to False?

Thank you kindly,

What about hiding the button after you disable it?

Sheet1.Shapes.Item(strButton).OLEFormat.Object.Vis ible = msoFalse

msoTrue unhides it.

Regards,

OssieMac



"Will Finkle" wrote:

Hi,

On recommendation from a textbook I was reading, I used Forms 1.0 toolbar
buttons to run macros, instead of using CommandButtons from the Controls
toolbar -- a decision I am now regretting. . . Anyway, I am using the
following code to temporarily disable buttons created from the Forms toolbar:

Sheet1.Shapes.Item(strButton).OLEFormat.Object.Ena bled = False

and this does disable the button from calling its OnAction macro. But the
button still appears the same, i.e. the text is not greyed out, and when you
move the mouse over the button, the pointer icon still turns into the "hand".
It is confusing to users, so I would like to make the button's appearance
reflect its current state.

Is there a way to modify the appearance or behavior for this type of button,
other than changing its Visible property to False?

Thank you kindly,

Hi Will,

I had problems posting this the first time so there is a possibility you
will get it twice.

What about hiding the button after disabling it?

Sheet1.Shapes.Item(strButton).OLEFormat.Object.Vis ible = msoFalse

of course msoTrue unhides it.

Regards,

OssieMac

"Will Finkle" wrote:

Hi,

On recommendation from a textbook I was reading, I used Forms 1.0 toolbar
buttons to run macros, instead of using CommandButtons from the Controls
toolbar -- a decision I am now regretting. . . Anyway, I am using the
following code to temporarily disable buttons created from the Forms toolbar:

Sheet1.Shapes.Item(strButton).OLEFormat.Object.Ena bled = False

and this does disable the button from calling its OnAction macro. But the
button still appears the same, i.e. the text is not greyed out, and when you
move the mouse over the button, the pointer icon still turns into the "hand".
It is confusing to users, so I would like to make the button's appearance
reflect its current state.

Is there a way to modify the appearance or behavior for this type of button,
other than changing its Visible property to False?

Thank you kindly,

Hi, Anant,

Thank you, this is very helpful indeed, and although it is definitely a good
solution and reasonable, my UI has 8 buttons that get enabled in 2's & 3's,
and it seems maybe unnecessary -- though I am going to get some feedback from
users tomorrow, and then I can decide whether or not this is necessary. I
just don't like the idea of having to insert 4 constant strings for each
button (2 captions, 2 OnActions...) but there are worse things in life! :-)

By the way, does anyone have an opinion on which buttons seem to be easier
to work with? It seems like these were really easy to initially assign to a
macro, with almost no overhead (i.e. no event handling) but the downside
seems to be the limited UI-configuration options. Any thoughts one way or
another?

thanks 1,000,000,

"Anant Basant" wrote:

Hi Will,

I also faced a similar problem in one of my projects and I applied the
following idea. Since the button can't be grayed out (or if it can I don't
know), I changed the caption of the macro to something like disabled and
changed the OnAction property to a macro that provides a friendly message.
When I wanted to change the button to an Enabled state from some other code,
I called "CallMyButton" macro with a TRUE switch.

I am providing the code below. Hope that solves your problem to some extent.

Option Explicit

Sub CallMyButton()
EnableMyButton True
End Sub

Sub EnableMyButton(bEnabled As Boolean)
Dim btn As Button
Set btn = Sheets("Sheet1").Buttons("Button 4")
If bEnabled Then
With btn
.Caption = "Enabled"
.OnAction = "HelloWorld"
End With
Else
With btn
.Caption = "Disabled"
.OnAction = "DisableButton"
End With
End If
End Sub

Sub HelloWorld()
MsgBox "Hello World"
End Sub

Sub DisableButton()
MsgBox "Sorry!!! You can't run the macro."
End Sub

--
Anant


"Will Finkle" wrote:

Hi,

On recommendation from a textbook I was reading, I used Forms 1.0 toolbar
buttons to run macros, instead of using CommandButtons from the Controls
toolbar -- a decision I am now regretting. . . Anyway, I am using the
following code to temporarily disable buttons created from the Forms toolbar:

Sheet1.Shapes.Item(strButton).OLEFormat.Object.Ena bled = False

and this does disable the button from calling its OnAction macro. But the
button still appears the same, i.e. the text is not greyed out, and when you
move the mouse over the button, the pointer icon still turns into the "hand".
It is confusing to users, so I would like to make the button's appearance
reflect its current state.

Is there a way to modify the appearance or behavior for this type of button,
other than changing its Visible property to False?

Thank you kindly,

Hi, OssieMac,

Thank you for the suggestion. I had noticed the visible property, but I
think that would look in my UI, if the buttons were not visible... although,
since they get enabled as the user performs Step1, then Step2, etc... it
might not be so bad. . . I'll check it out.

Thanks again,

"OssieMac" wrote:

Hi Will,

I had problems posting this the first time so there is a possibility you
will get it twice.

What about hiding the button after disabling it?

Sheet1.Shapes.Item(strButton).OLEFormat.Object.Vis ible = msoFalse

of course msoTrue unhides it.

Regards,

OssieMac

"Will Finkle" wrote:

Hi,

On recommendation from a textbook I was reading, I used Forms 1.0 toolbar
buttons to run macros, instead of using CommandButtons from the Controls
toolbar -- a decision I am now regretting. . . Anyway, I am using the
following code to temporarily disable buttons created from the Forms toolbar:

Sheet1.Shapes.Item(strButton).OLEFormat.Object.Ena bled = False

and this does disable the button from calling its OnAction macro. But the
button still appears the same, i.e. the text is not greyed out, and when you
move the mouse over the button, the pointer icon still turns into the "hand".
It is confusing to users, so I would like to make the button's appearance
reflect its current state.

Is there a way to modify the appearance or behavior for this type of button,
other than changing its Visible property to False?

Thank you kindly,