Macro to hide an ActiveX Controls checkbox??

Secher

New Member
Joined
Oct 8, 2014
Messages
3
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?
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

NewOrderFac33

Well-known Member
Joined
Sep 26, 2011
Messages
1,252
How about something like:
Code:
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
Code:
 UserForm1.Controls("CheckBoxA").Visible = False
 UserForm1.Controls("CheckBoxB").Visible = False
 UserForm1.Controls("CheckBoxC").Visible = False

and so on.

Hope this helps

Pete
 
Last edited:

Secher

New Member
Joined
Oct 8, 2014
Messages
3
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

Well-known Member
Joined
Sep 26, 2011
Messages
1,252
Afraid not - work email filters.
In what way did it not work - what error message did you receive?
 

NewOrderFac33

Well-known Member
Joined
Sep 26, 2011
Messages
1,252

ADVERTISEMENT

Ah - it's a worksheet checkbox.

Try something like this:
Code:
Sheets("Sheet1").CheckBoxes("Check Box 1").Visible = False

To toggle between visible and visible, use:
Code:
Sheets("Sheet1").CheckBoxes("Check Box 1").Visible = Not(Sheets("Sheet1").CheckBoxes("Check Box 1").Visible)

Pete
 

NewOrderFac33

Well-known Member
Joined
Sep 26, 2011
Messages
1,252
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:
Code:
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

New Member
Joined
Oct 8, 2014
Messages
3

ADVERTISEMENT

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

Well-known Member
Joined
Sep 26, 2011
Messages
1,252
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:
Code:
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:
Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,911
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top