Hiding Checkboxes?

jkharmer · Jul 20, 2008

Hi,

I am trying to use the following code:

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 12"><meta name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CUsers%5CJAMESH%7E1%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CUsers%5CJAMESH%7E1%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CUsers%5CJAMESH%7E1%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style>  </style> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 12"><meta name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CUsers%5CJAMESH%7E1%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CUsers%5CJAMESH%7E1%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CUsers%5CJAMESH%7E1%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style>  </style> ActiveSheet.Shapes("Check Box 391").Visible = True

and modifying it to:

If Sheets("TPM").Range("al13").Value = 2 Then ActiveSheet.Shapes("Check Box 391").Visible = True

The first one works OK, but when I try to add an if / then statement, it gives me an error message.

Can this be done? Ideally I want to hide Check boxes 391 through to 426, using a single macro.

Any help would be great!

James

cadence · Jul 25, 2008

Tom thanks I got mine to work, so all sweet.

I think it was the % in "i%" that stuffed me up. I mean thank heaven for the forum and the helpers of people like you as its those little details that can send you insane if you don't know them, and where can you learn about those details!

What does the % actually represent?

One other tangent question: How can you apply a macro to a validation range, say that if a specific value in a range was selected eg apples it would trigger a macro? - is this possible?

Tom thanks again for your help its always very detailed.

Jkhammer
Also for Jkhammer if you are create many check boxes this little code is code for automating it and place on below the other on a particular column and assigning a cell for the output value.

It may need a tweak if you want to define [name] each check box as well.

I think BrianB was the original author

Hope it helps.

Code:

Private Sub AutomateCheckBox()
Application.EnableCancelKey = xlDisabled
Application.ScreenUpdating = False
Dim Sh As Worksheet
Dim x As Integer
Dim c As Range
'Dim ce As Range
Dim Left, Top, Width, Height
Dim CB As Object
Set Sh = Worksheets("Name") 'sheet name here
Application.ScreenUpdating = False
For x = 33 To 38 'Range of rows is tats and finishes at
Set c = Sh.Cells(x, 10) '10 is the R1C1 Column Ref.
Left = c.Left
Top = c.Top
Width = c.Width
Height = c.Height
Set CB = ActiveSheet.CheckBoxes.Add(Left, Top, Width, Height)
With CB
'.Caption = c.Address(False, False)
.Caption = "" 'no caption
.Characters.Text = ""
.ShapeRange.ScaleWidth 0.25, msoFalse, msoScaleFromTopLeft 'makes the text box small
'.ShapeRange.IncrementLeft 106#  'move the alingment
.ShapeRange.IncrementTop -2#   'move the alingment
.PrintObject = False
.LinkedCell = St.Name & "!" & c.Offset(0, -98).Address 'links the output value to a cell
.Value = xlOn
.Value = xlOff
End With
Next x

Application.ScreenUpdating = True
MsgBox ("Done")
End Sub

Tom Urtis · Jul 25, 2008

cadence said:
What does the % actually represent?

The % symbol is VBA's abbreviation for the Integer data type.

Example,

Dim i%
is the same as
Dim i as Integer

Others:

% Integer
& Long
! Single
# Double
@ Currency
$ String

cadence said:
One other tangent question: How can you apply a macro to a validation range, say that if a specific value in a range was selected eg apples it would trigger a macro? - is this possible?

Yes it is possible, with a Change event in the worksheet module, example for cell A1:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
MsgBox Target.Value
End Sub

If you do a search on this board and/or on Google you will find thousands of examples of Change (and other event) code examples.

Recall, to access the worksheet module, right click on the worksheet tab, and left click on View Code. The worksheet module is the large white pane.

cadence · Jul 26, 2008

Toms Thanks for explaining that its those things that can drive you round the wall, thanks heavens for the forum.

Thaks for the info on validation, its a good way of doing it as I gather you do it directly.

I just have a slight tangent follow on question.

[I changed Check box to Drop Down]

I've been mucking round with this all day and haven't quite cracked it. I'm trying to get it toggle, so that empty rows and Drop Down hide. I've assigned the output of each drop down to a cell so that
Drop Down 1 = B7 ... Drop Down 12 = B18.

How can I keep the Drop down visible if its value is not empty?

Code:

Sub HideDropListRange()

Dim i As Integer '%
ActiveSheet.Range("A1") = Not ActiveSheet.Range("A1") = True
If ActiveSheet.Range("A1").Value = True Then

For i = 1 To 12
ActiveSheet.Shapes("Drop Down " & i).Visible = False
Next
Else
For i = 1 To 12
ActiveSheet.Shapes("Drop Down " & i).Visible = True
Next
End If

Set myrange = Range("B7:B18")
For Each C In myrange
  If C.Value = "" Then
  C.EntireRow.Hidden = Not C.EntireRow.Hidden
  End If
Next

End Sub

This is why I asked about validiation in case that this was not possible to.

thanks for any input you can offer.

johnny

cadence · Jul 30, 2008

Tom, I've tried doing this with a validation to trigger off cell A1, but it doe not seem to respond. I can manually type a number for it to display but if it works off a validation list ... nothing.

Any tricks I need to be doing?

Tom Urtis said:

thanks

Tom Urtis · Jul 30, 2008

Not totally clear what you are doing.

What does this mean that you wrote:
"so that empty rows and Drop Down hide"

Are you hiding rows and the checkboxes they are on?

Important, what constitutes an "empty row", is it that all 256 cells (or 16384 in 2007) are empty, or just some, or just 1, or what.

Any details you can provide will help.

Also, it does not help matters that you are double-posting this question, all it does is confuse things.

http://www.mrexcel.com/forum/showthread.php?t=331332

cadence · Jul 30, 2008

Tom,

Are you hiding rows and the checkboxes they are on?

Yes, so I have a drop down on Row 7, a cell link in B7, repeat down to Row 18

Important, what constitutes an "empty row"

An empty row within the range B7:B18

This is where I got up to, but
I get an run time error 438 object doesn't support this property or method" on line
If ActiveSheet.Shapes("Drop Down " & i).Value = "" Then

Rich (BB code):

Sub HideDropListRange2a()

Dim i As Integer '%
ActiveSheet.Range("A1") = Not ActiveSheet.Range("A1") = True
If ActiveSheet.Range("A1").Value = True Then

For i = 1 To 12
 If ActiveSheet.Shapes("Drop Down " & i).Value = "" Then
    ActiveSheet.Shapes("Drop Down " & i).Visible = False
End If
Next
Else
For i = 1 To 12
    ActiveSheet.Shapes("Drop Down " & i).Visible = True
Next
End If

Set myrange = Range("B7:B18")
For Each C In myrange
  If C.Value = "" Then
  C.EntireRow.Hidden = Not C.EntireRow.Hidden
  End If
Next

End Sub

Alternative example
PS I was also persevering with the alternative approach you suggested and had trouble getting the validation of the cell A1 to trigger the "Private Sub Worksheet_Change(ByVal Target As Range)"

Hiding Checkboxes?

jkharmer

Board Regular

cadence

Well-known Member

Excel Facts

Tom Urtis

MrExcel MVP

cadence

Well-known Member

cadence

Well-known Member

Tom Urtis

MrExcel MVP

cadence

Well-known Member

Similar threads

Forum statistics

Share this page

Hiding Checkboxes?

jkharmer

Board Regular

cadence

Well-known Member

Excel Facts

Tom Urtis

MrExcel MVP

cadence

Well-known Member

cadence

Well-known Member

Tom Urtis

MrExcel MVP

cadence

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock