Cannot Find Button From Control Properties

[cdun2]

Hello,
On my worksheet, I have dropped a button control from the Forms
Toolbar. I have aasigned a macro to the button. The on click event
(??) of the button fires the macro properly, and I get the results I
expect. However, a cell has to be edited before the button can be
clicked. Before the button is clickable, I have to be out of edit
mode.

In VBA, I cannot find a way to reference this button control. The
button is not in a form, its embedded within the worksheet. I would
like to be able to exit edit mode when I mouseOver the button.

What should I do?

Thank you for your help!

cdun2

 

[Guest]

You are out of luck on that one. No code will run while a cell is in edit
mode. No events fire... Nothing...

That being said you can reference a button from the forms toolbar like this...
MsgBox Sheets("Sheet1").Buttons("Button 1").Caption

 

[Guest]

Here is a solution you might want to persue. When the cell is finished being
edited you could make the button visible (or enabled although that
functionallity is not to great in buttons from the forms toolbar as the
button is not greyed out). Plece code similar to this in the sheet where you
have the cell to be edited...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then 'Where A1 is the edited cell
Sheets("Sheet1").Buttons("Button 1").Visible = False
else
Sheets("Sheet1").Buttons("Button 1").Visible = True
End If
End Sub

This code is only a sample and not intended as your final code. I am unclear
as to when this code is valid to be run and how you want the button to work...

 

[cdun2]

Thanks for this input, I'll try this approach.

Here is a solution you might want to persue. When the cell is finished being
edited you could make the button visible (or enabled although that
functionallity is not to great in buttons from the forms toolbar as the
button is not greyed out). Plece code similar to this in the sheet where you
have the cell to be edited...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then 'Where A1 is the edited cell
Sheets("Sheet1").Buttons("Button 1").Visible = False
else
Sheets("Sheet1").Buttons("Button 1").Visible = True
End If
End Sub

This code is only a sample and not intended as your final code. I am unclear
as to when this code is valid to be run and how you want the button to work...
--
HTH...

Jim Thomlinson







- Show quoted text -