check boxes

[ranswert]

I have the following code that adds a checkbox to my worksheet:

Sub addcheckbox()
Dim a As String
Dim costid As String
Dim cboxname As Integer
Dim cell As Range
Dim chk As Excel.CheckBox
Set cell = ActiveCell
Set chk = cell.Parent.CheckBoxes.Add(cell.Left, cell.Top, 12, cell.Height)
costid = id
MsgBox ("costid =" & costid)
chk.Height = cell.Height - 1.5
cboxname = ActiveCell.EntireRow.Cells(13)
chk.Characters.Text = none
chk.name = "Checkbox" & costid & cboxname
chk.LinkedCell = ActiveCell.EntireRow.Cells(14).Address
ActiveCell.Select

End Sub

When I go to hide the range that contains the check box, the check box isn't
hidden. Is the a property that I need to set to hide the check box?
Thanks

 

[Jim May]

Checkbox has a visible property - the default is True

 

[michael.beckinsale]

Hi ranswert,

If you are using a 'form' checkbox there are no properties available
to make it invisible but you could change the line colours etc to make
it appear to be invisible.

If you use the 'control toolbox' checkbox there is a visible property
(boolean) you can set as required.

HTH

Regards

michael beckinsale

 

[ranswert]

Do I need to change the visible propert to false when I want to hide the
range and to true when it is not hidden?

 

[ranswert]

I also am using the following code to hide everything but the range I want to
show:

Sub HideAroundSelection()
Dim intRows As Integer
Dim intCols As Integer
Dim rngAbove As Range
Dim rngRight As Range
Dim rngBelow As Range
Dim rngLeft As Range


intRows = Selection.Rows.Count
intCols = Selection.Columns.Count
MsgBox ("introws = " & intRows & vbLf & "intcols = " & intCols)
With Selection
Set rngAbove = .Cells(1, 1).Offset(-1, 0)
Set rngBelow = .Cells(1, 1).Offset(intRows, 0)
Set rngRight = .Cells(1, 1).Offset(0, intCols)
'Set rngLeft = .Cells(1, 1)

If rngAbove.Row <> 1 Then
Range(rngAbove.Offset(-1, 0), .Cells(1, 1). _
Offset((1 - .Cells(1, 1).Row))).EntireRow.Hidden = True
End If
If rngBelow.Row <> ActiveSheet.Rows.Count Then
MsgBox ("Entered if for rngbelow" & vbLf & "row = " &
rngBelow.Row)

Range(rngBelow.Offset(1, 0), rngBelow.Offset _
(ActiveSheet.Rows.Count - rngBelow.Row)).Select
MsgBox ("")

Range(rngBelow.Offset(1, 0), rngBelow.Offset _
(ActiveSheet.Rows.Count - rngBelow.Row)).EntireRow.Hidden = True
End If
If rngRight.Column <> ActiveSheet.Columns.Count Then
Range(rngRight.Offset(0, 1), rngRight. _
Offset(0, ActiveSheet.Columns.Count -
rngRight.Column)).EntireColumn.Hidden = True
End If
'If rngLeft.Column <> 1 Then
'Range(rngLeft.Offset(0, -1), rngLeft. _
'Offset(0, 1 - rngLeft.Column)).EntireColumn.Hidden = True
'End If

End With
Set rngAbove = Nothing
Set rngRight = Nothing
Set rngBelow = Nothing
Set rngLeft = Nothing


End Sub

When it get to the part where it hides the range below the selection I get
an error.
When I try to do is manually i get the error 'cannot shift objects off
sheet". Will changing the checkbox.visible property to false fix this?
Thanks

 

[Dave Peterson]

If you used a checkbox from the Control toolbox toolbar instead of a checkbox
from the Forms toolbar, you could change a property to move and size with cells.

You could record a macro to get the syntax.

 

[ranswert]

I tried to do that but that I am not able to select that option. I am able
to select the other two options.

 

[Dave Peterson]

Try it using the checkbox from the Control toolbar toolbar--not the Forms
toolbar.