Macro to hide an ActiveX Controls checkbox??

[Secher]

I have in cell E5 a drop down list where i can choose between the following: New Project Quote and Sub Project.

When I choose Quote a lot of cells become formatted ith conditional formatting.
But I hve 8 checkboxes which doesn't disappear when the ormatting is activated.

Can I create a macro where these check boxes disappear when i choose Quote in the drop down list?

 

[NewOrderFac33]

How about something like:

For i = 1 To 8
    UserForm1.Controls("CheckBox" & i).Visible = False
Next i

or, if your checkboxes don't follow this naming convention, just duplicate the line within the For-Next loop for each checkbox

 UserForm1.Controls("CheckBoxA").Visible = False
 UserForm1.Controls("CheckBoxB").Visible = False
 UserForm1.Controls("CheckBoxC").Visible = False

and so on.

Hope this helps

Pete

 

[Secher]

Hi Pete.

Thx for quck response.
I couldn't get it to work.

If i send you the file, could you try and see if you could get it to work?

 

[NewOrderFac33]

Afraid not - work email filters.
In what way did it not work - what error message did you receive?

 

[NewOrderFac33]

Ah - it's a worksheet checkbox.

Try something like this:

Sheets("Sheet1").CheckBoxes("Check Box 1").Visible = False

To toggle between visible and visible, use:

Sheets("Sheet1").CheckBoxes("Check Box 1").Visible = Not(Sheets("Sheet1").CheckBoxes("Check Box 1").Visible)

Pete

 

[NewOrderFac33]

To activate the checkbox hiding based on a change to E5, you would need to paste the following into the worksheet's code module, NOT a standard code module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E5")) Is Nothing Then
        If UCase(Target.Formula) = "NEW PROJECT QUOTE" Then
            Sheets("Sheet1").CheckBoxes("Check Box 1").Visible = False
            Sheets("Sheet1").CheckBoxes("Check Box 2").Visible = False
            'and so on...
        End If
    End If
End Sub

Hope this helps

Pete

[/code]

 

[Secher]

I give up.
when I tried to enter the code, the check box didn't work anymore, and kept showing n error.

Thx for the try Pete

 

[NewOrderFac33]

I have sent you a message.

 

[NewOrderFac33]

Nete,

In answer to your comment about posting the code into your version of the workbook and it not working, I would imagine that in your version, the worksheet name still contains the space (I don't have it in front of me, but if the sheet is called "Credit " and the code tries to rererence the checkboxes like this:

Sheets("Credit").checkboxes("Check Box 32").visible = false

you'll probably get a "Subscript out of range" error message i.e. the code can't find a sheet called "Credit" because in your workbook it's called "Credit " (with a trailing space)

You either need to rename the worksheet to remove the space or alter the code so that the references to the worksheet name INCLUDE the space - i.e. they must match, whichever way you do it.

It can be a problem to rename worksheets in this way, as you don't always know what OTHER code refers to their names!
One way around it is to use TRIM(), which removes trailing and leading spaces (but not those between the words) from a string thus:

If Trim(UCase(Target.Formula)) = "NEW PROJECT QUOTE" Then

This way, it doesn't matter about the spaces as your check will remove them.

Similarly, the original worksheet range that is referenced by E5 included a cell "Quote " (with a trailing space), so I changed my code to reflect that, as I couldn't unhide the sheet containing the range to make the change there.

Hope this makes sense - please feel free to send me any other problems you encounter - I only have to speak to my wife otherwise!

Pete