Command button stops working after running other macros

[Bearz]

I am working on a workbook with a number or simple macros. They all work correctly if run independently, but they seem to be interfering with one another and I, being a VBA novice, can't figure out how or why.

The two in question are a command button which performs a goal seek function,

Please Login or Register  to view this content.

and a checkbox that hides displays or hides columns and the command button when selected.

Please Login or Register  to view this content.

The goal seek works fine at first, but if the checkbox is deselected and then selected again - hiding and then re-displaying the goal seek button - it seems to lose its functionality. However, if I go into the VBA and run the goal seek on its own, it still works.

Additionally, if go into design mode and edit the command button object, it shows the button as having "moved" to the right. If I place it back over the actual button, the functionality is restored. How can I keep it from shifting?

I have attached an excel sample which has the issue. Any help on resolving this issue would be appreciated. Thanks in advance.

[TMS]

I put some debug statements in your routines and they seem to perform as expected.

Please Login or Register  to view this content.

They are both click event handlers so, if Event handling has been disabled, perhaps in another application, it would affect the execution of this code. Obviously, there is nothing in this code that disables event handling.

Regards, TMS

[JasperD]

Your sample works fine...

[Bearz]

Hmm... Unfortunately this doesn't seem to have resolved my issue. I edited the VBA in the example workbook and have closed all other workbooks so there shouldn't be any other code interfering with this workbook. However, I am having identical issues as before.

Any other ideas? Thanks again.

[TMS]

In the Immediate Window, type

Please Login or Register  to view this content.

and press Enter

If it comes back False, type

Please Login or Register  to view this content.

and press Enter


Regards, TMS

[Bearz]

Thanks TMS,

Entering the query into the immediate window yields a result of "True"tering the second half "... =True" doesn't seem to cha

The problem still persists...

[xladept]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Bearz]

This is the same file as the one from earlier in the thread, with the exception that the debugging additions have been made to the code.

There is example data in cells B14:B16. Cell B17 has a formula calculating based on the values in the three cells above.
The goalseek button sets the value in B14 such that the formula in B17 equals 95. This button works at the outset.
If the checkbox is unchecked (this hides the goalseek button) and then checked again (bringing the button back into view) the function of the button no longer works.The version I have attached here has already hidden and displayed the button, so (on my computer at least) the goalseek button no longer works. When I click and hold down the mouse button over the goalseek button, an identical goalseek button appears to the right.

Thanks again.

[TMS]

Your computer, I'm afraid. I can run this till the cows come home and it won't fail. I can hide and unhide the columns, I can delete the initial value in cell B14, before and after hiding the Goal Seek button.

It's not in this sample workbook and, if event handling has not been switched off externally, I have no idea what is causing this.

[Bearz]

After some more tinkering, I was able to resolve the issue by going to Format Control >> Properties >> and the object positioning to "Don't move of size with cells"

[TMS]

Good ... but not sure why a couple of us can run it without an issue.

Whatever ...


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.