ActiveX CommandButton becomes non-clickable after making it invisible and visible

[liyunyi]

Hi guys,

I encounter with a problem that Commandbutton works well before hiding it. But once making it hidden and back, the button suddenly does not react to click_event anymore. I have to close the excel and reopent it, then it recovers.

I just have searched the problem. There are similar questions shown but could not find a proper answer. Can anyone helps? Much Appreciated!

An exactly same situation there: https://answers.microsoft.com/en-us...ly-after/a73aa0a1-5eef-4684-8118-f8b99c996c0c

In my case, I want to filter the table and at the meanwhile hide the commandbutton with caption corresponding to unchosen row, plesae see the image

 

[Micron]

There are similar questions shown but could not find a proper answer.

I don't see what's wrong with the answer given at the link. The problem seems to be that activex controls on a sheet don't behave well after hiding. The explanation given is that you see the button but it's not really there. The solution given is to run code to reposition them to where they should be. If you don't like that idea, consider using form control button instead, or disabling commandbutton instead of hiding, or trapping the click event if the condition you use to hide the button happens to exist.

 

[liyunyi]

Genius! Thats exactly what happen. Sry I couldnt understand the answer at first time. Your words inspired me. Yes, after unhiding the button appears on the cells which is not really there. I have looked into the TopLeftCell.Address from Attribute of ActiveX control, it changes. Therefore, I do what you say, reposition it to where it appears on. It works well now!

Thanks

 

[Micron]

Glad I was able to help!