Hide Objects With Hide Rows Feature

  • Thread starter Thread starter SmileyEyes
  • Start date Start date
S

SmileyEyes

I currently have several rows that contain checkboxes. The
checkboxes are linked to cells which they reside on. At
certain times of the spreadsheets life, certain rows need
to be hidden. I have no trouble hiding the rows, but I do
have trouble hiding the checkboxes that are linked to the
cells that reside in the rows.
This is creating problems, because the checkboxes actually
reside on top of the next rows, and therefore the cell
linkage of the checkboxes appears incorrect. For example:
if I have a spreadsheet with checkboxes in column C, and I
hide rows 2-5, the checkboxes that are linked to those
cells (c2:c5) appear on top of cell(c6). But, it looks
like they are not there.
I feel as though I am missing something. There is a
feature within Excel that allows users to hide ALL
objects, but what about ALLL objects within a particualr
row??
If anyone knows anything about this, I willl be ever so
grateful!
SmileyEyes
 
Smiley,

Change tack.

Another way is to use this technique of having a check column, and
monitoring it with a worksheet selection change event. Add your code as
needed.

Rather than use a checkbox, I suggest just using a check column. So if we
assume that the data is in A1:E100 (change to suit), clicking in column A
will do what you want with this code. Add this code to the worksheet module
(right-click on the sheet name tab, select the View option, and then paste
this code in).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo sub_exit
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
With Target
If .Value = "a" Then
.Value = ""
Else
.Value = "a"
.Font.Name = "Marlett"
End If
End With
End If

sub_exit:
Application.EnableEvents = True
End Sub
 
And if you want to have a solution to a problem that doesn't exist anymore, you
could have used the checkboxes from the controltoolbox toolbar.

Right click on one and select Format Control|Properties Tab
Check Move and size with cells.

(The checkbox from the forms toolbar doesn't support this.)
 
Back
Top