Control becomes a pic

[vbquery]

Hi Everyone,

I am a fairly new VBA programmer and am having an issue where a control is no longer a control. I'm trying to figure out why it no longer is a control.

I have a worksheet with checkboxes on it from the control toolbar. I have code that runs when the user clicks the checkbox. Sometimes when the spreadsheet comes back to me from a client or associate, the checkbox is no longer a control - i.e. when I click on it, the code does not run. When I hover over it, it appears to be a picture or shape. Excel will surrond the checbox with a rectangle with the circles - as if I could drag it out to make the shape bigger.

Thanks to this forum, I have figured out how to remove the shape and add the checkboxes back into the worksheet in my VBA code. When I do that, I rename the boxes and the code once again works. My concern is why am I losing the control?

I also have another worksheet where the control is a listbox - again from the Control Toolbox. The same thing will sometimes happen with that.

In one case, I copied the information from one spreadsheet to my 'empty' spreadsheet and sent it back. That time the new spreadsheet worked fine on my associates computer.

Thanks

 

[Andrew Poulsom]

Welcome to the Board.

That will happen when in Design Mode. Click the icon at the top left of the Control Toolbox to exit.

 

[vbquery]

Welcome to the Board.

That will happen when in Design Mode. Click the icon at the top left of the Control Toolbox to exit.

Thanks for the quick response Andrew.

Are you saying the user is selecting design mode, or that I have inadverntly left the 'empty' spreadsheet in design mode the last time I saved it? My VB code builds a client spreadsheet by openeing the 'empty' and then saving it as a new name.

Is there any way to prevent the user from entering design mode if that is the problem? I need the client to be able to add worksheets as well be able to paste information on the worksheets my VBA code creates.

Thanks

 

[Andrew Poulsom]

I don't know what is activating design mode, but this should clear it:

Sub ExitDesignMode()
    With CommandBars("Exit Design Mode").Controls(1)
        If .State = msoButtonDown Then .Execute
    End With
End Sub

 

[vbquery]

I don't know what is activating design mode, but this should clear it:

Sub ExitDesignMode()
    With CommandBars("Exit Design Mode").Controls(1)
        If .State = msoButtonDown Then .Execute
    End With
End Sub

Hi Andrew,

Thanks again for your help.

Do I put the sub you gave me on ThisWorkbook or somewhere else in the code? Also, will I need to call the code I wrote to rebuild the controls? I'm sorry if these are basic questions.

Thanks,

Ralph

 

[Andrew Poulsom]

Sorry, I have no idea where you would put the code. I don't know what is causing the problem or what your current code does.

 

[vbquery]

Sorry, I have no idea where you would put the code. I don't know what is causing the problem or what your current code does.

I think I figured out that your code must be checking the spreadsheet is in design mode and switching it back to execute mode. I thought it was going to execute some code or something.

I think I'll go ahead and put the check in my ThisWorkbook module and run the code if the spreadsheet is in design mode.

Thanks for your help. I really appreciate it.