Problem with command buttons on protected sheets

[stduc]

I have a worksheet with various command buttons on it, to update associated charts etc after new data is added. This all works just fine. I protect the sheet again in order that only data cells, not formula containing cells can be edited and it still works fine.

Then I save the workbook and re-open it - now I can't click on the command buttons, even if I unprotect the sheet. What's going on?

 

[SkipVought]

Hi,

What kind of control -- Control Toolbox, Forms, Data/Validation???

if a Control Toolbox control, is it in Design Mode?

if a Forms control, check the Assign macro link. You could have lost it. I assign Forms contol links in the Workbook_Open event.

Skip,

_[red][/red]

 

[stduc]

It's a simple command button. I can cut & paste it and it works again once I re-associate the macro. Until I save & re-open the workbook. I have no problems with a fresh workbook that I have created an example one one.

I have tried re-booting.
I have tried repairing the workbook.

I am thinking the workbook is corrupt in some strange way & my only option may be to re-create it - quite a bit of work though.

This is the macro behind the button.

Private Sub CommandButton1_Click()
Call UpdateChart("Data", "Chart1", "LastCell")
End Sub

 

[stduc]

OK - I've solved the problem. I was playing around with my simple test example and opened a second view of the sheet. On the second view the button was not clickable, why?

So I closed all the views except one on the problem worksheet and hey presto - back to normal. So I re-opened the views and - still no issues.

While I am happy I know now what happened and what to do if it happens again.

 

[SkipVought]

What kind of control -- Control Toolbox, Forms, Data/Validation???

It's a simple command button.

There are no Simple controls, in light of the question asked.


From the information you supplied, I deduce that you have a Control Toolbox control. Glad your issue is solved.

Skip,

_[red][/red]

 

[stduc]

As a supplemental question - is this normal excel behaviour that a control won't work in the second window - or is that behaviour controlled by an option somewhere?

I have been known to be wrong.
The best way to thank someone who helps you is give them a star.

 

[SkipVought]

Please explain, along with offending code, what is NOT working on another sheet.

Skip,

_[red][/red]

 

[stduc]

Starting with a new workbook. If I

Create a new command button using the control toolbox which is given the name - CommandButton1 and the formula
=EMBED("Forms.CommandButton.1","")

I then create the following macro associated with this control

Private Sub CommandButton1_Click()
MsgBox "HI THERE"
End Sub

If I exit design mode and click the button - I get the message box "HI THERE"

If I then use window > new window to open a second view of the same worksheet. The button is "greyed out".

[/LINK]

Is this normal behaviour or can you influence it through use of options somewhere?

FYI.

On the production worksheet there are three windows open - to three different sheets. A data sheet, a result sheet and a chart. The buttons are on the data sheet and control the recalculation and appearance of the other two sheets.

I changed the macros' slightly and suddenly after closing and re-opening the workbook, the buttons stopped being clickable. But they were not 'greyed out'. Eventually the behaviour was stopped by closing the result and chart windows - then re-opening these windows. Closing and re-opening the workbook did not cause the issue to re-appear. I then re-sized and re-positioned the windows to they way they were before and closed/re-opened the workbook. Again - no problems - the buttons all worked.

So all though it is a mystery as to why it happened in the first place - I'll just add it to my mental list of things to check in future when excel misbehaves!

 

[SkipVought]

When I opened a new window, I got the grayed out button.

It must be that the control is associated with both the sheet and original window.

Is it really necessary to have the button active in both windows?

Otherwise, consider a modeless userform/button.

Skip,

_[red][/red]

 

[Asspin]

Would protecting it via userinterface only not work here?

Dan

 

[combo]

Activex command button responds only in first worksheet's window. The appearance can be improved after setting TakeFocusOnClick to false, anyway it still will not respond.
You can use a button from 'Forms' toolbar, anyway there is a price for it. Have you considered creation of own toolbar to manage the project?

combo

 

[stduc]

Asspin
Would protecting it via userinterface only not work here?

How do you mean? Can you explain please?

comboHave you considered creation of own toolbar to manage the project?

I have to confess I have never had much joy with creating a toolbar and adding macro buttons to it. It seems to break all too easily to me. But maybe I have been doing it the wrong way? I'll look into the forms toolbar.

 

[combo]

Just to explain it a bit:

1. Skip asked about buttons you use, could be old excel versions heritage in 'Forms' toolbar, or newer from 'Toolbox' (Activex controls). Your future posts indicate that you use the second ones. You can use 'Forms' button and assign a macro in standard module to it.

2. You can create your own toolbar. You can do it manually, right-click any of toolbars and choose 'customize', next add a bar and controls, and assign macros. The better way is to do all by code when you open a workbook, destroy it on close. There is 'Office' library that controls commandbars. Anyway the simplest way is, as Skip suggested, modeless userform (MSForms).

combo