Hide/Unhide buttons via event procedure 1

[NIWANut]

Hi,

I have an event procedure that is triggered when any data in the sheet is changed, part of this event should unhide (make visible) a command button to re-calculate other information in the workbook.

I am having trouble hiding and unhiding the command button. I have used the Control toolbox to place my command button on the sheet of interest but I cannot make the button visible (I want it invisible initially). I have set the visible property of the button to be false but when the workbook is opened and the sheet activated the button is always visible. I tried via my code in the event procedure to make the button invisible(opposite to what I want but as it is always visible I thought I could test it by making it invisible) but that does not work either, I used the line

Calculate.visible=False

but this just causes an error (I named my button "Calculate"). I also tried Calculate.Hide but that produces the same error which is "Object required"

I noticed another post that suggested the controls from the control box can be problematic and to use the controls via the Forms toolbar but if I use those I cannot get to the properties of the button at all. I thought about using a userform but I want the user to be able to work with the data on the sheet and once finished to push the command button to re-calculate, the idea is that if no changes are made the button is not visible, but once a change is made the button becomes visible to remind the user he must recalculate. For specific reason I do not wwant the calculations to occur until all changes are made.

I am using Excel 2000 under Windows XP.

Thanks



George

 

[PHV]

Have you tried something like this ?
ActiveSheet.OLEObjects.Visible = False

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TonyJollans]

Hi NIWANut,

Calculate is not a valid name name for a button. How have you named it this?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[NIWANut]

Hi Tony,

I have used the Control Toolbox to place a Command Button on the sheet. Right Clicking this gives you the option to open the Properties of the command button where the default name for the button of "CommandButton1" can be changed, I changed it to Calculate. You can also embed similar buttons from the Forms toolbar. When this is done a window pops up asking to link to the macro you want run when the button is clicked. When you embed buttons from this procedure if you right click on them you do not get a properties choice, so I do not know how to refer to these buttons in VBA.

I thought this was going to be quite an elegant solution to my problem of putting something in front of my users to remind them they need to re-calculate the outputs once all the data changes are made.



George

 

[PBAPaul]

Hi George

I think I have had the same type of problem that you describe and have gone all around the houses to resolve it.

I use the code (in Excel XP)

Sheets("Sheet1").Shapes("Calculate_Button").Select
If RecalcRequired = False Then
  Sheets("Sheet1").Shapes("Calculate_Button").Fill.Visible = msoFalse
Else
  Sheets("Sheet1").Shapes("Calculate_Button").Fill.Visible = msoTrue
End if

This might not be exactly what you are looking for but it might be worth a try.

Paul

 

[TonyJollans]

Hi George,

Thanks for the very clear explanation. When I do this it gives me an error message because Calculate is already defined which is why I asked.

If your code is in the same sheet as the button you should be alright (and I can think of no reason beyond the name why it should fail) but if it is anywhere else (ThisWorkBook or a Module), you need to be more specific in referencing it, for example

[blue][tt] Activesheet.Calculate.Visible = True[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[NIWANut]

Hi Tony,

You deserve a star(which I have given you), a very simple answer that works just as I wanted.

In my Workbook macro I made my button on that sheet invisible when the workbook was opened.

When a user changes any data on the raw data sheet, the event procedure makes the button visible and it stays visible until the user clicks on it to re-calculate the data when it is again made invisible.

A very elegant answer to my updating problems, I think I can even set up a simple process to check to see if the user saved the workbook without updating, in which case the button will be made visible at startup.

Wow, this cool! Thanks

George