Dynamic Ranges/Cells??
 

Hi

I have a spreadsheet with macros assigned to command buttons that
hide/unhide rows.....i also have a watched range (actually 2 ranges
joined by a union) that colours cells depending on the value...

What I want to be able to do is add a row or column and have the range
extended automatically .....is this actually possible....

Also, the command button works by call a function (passing the row
location as a variable) which calculates the next 5 lines and hides
them...see the code below:

Sub hideunhide(cellloc)

Application.ScreenUpdating = False
Dim cellstart, cellend As Long
cellstart = cellloc + 1
cellend = cellloc + 5

Dim RangeSelect As Range
Set RangeSelect = Range("b" & cellstart, "b" & cellend)
RangeSelect.Select

If Selection.EntireRow.Hidden = False Then
Selection.EntireRow.Hidden = True
Range("a" & cellstart).Select
Application.ScreenUpdating = True

Else
RangeSelect.Select
Selection.EntireRow.Hidden = False
Range("a" & cellstart).Select
Application.ScreenUpdating = True

End If

End Sub

The only problem is that I have to manually enter the "cellloc" when
calling the function i.e

Private Sub CommandButton1_Click()
hideunhide (5)
End Sub

This is fine but I literally have hundreds of buttons and when I have
to insert one row it throws everything out from that line on...

I am obviously a novice to Excel and VBA so please don't punish me!!
Any suggestions on how I should go about it are welcome :-)

one last thing...is there a way of using VBA to find out what cell or
line a button is located??? either through it's properties or
otherwise...this would go a long way to solving my problem!!

Thanks
Glenn


--
glpabr
------------------------------------------------------------------------
glpabr's Profile: http://www.excelforum.com/member.php...o&userid=23967
View this thread: http://www.excelforum.com/showthread...hreadid=375894

Dynamic Ranges/Cells??
 

As for inserting rows: First, I suggest you use a named range, so your code
can refer to the range by name and not address; that way even if the range
changes size (e.g. row added) the code will work without modification. The
only thing to be careful of is adding rows: to keep the named range together
you need to insert a row above the last row in the existing range and then
copy/paste your last line up into the inserted row.

For the second question, it is hard to answer without knowing what cellloc
is supposed to be: are you referring to the active cell's row number? If so,
that would be ActiveCell.Row, and you could use this to replace cellloc in
the code.

Or perhaps cellloc is the location of the button, which would explain your
third question. The answer there is that buttons do have a property to tell
you what cell they are in (at least where the top left corner is). It is
different depending on how you created the button: if from the Forms toolbar
it is:
Sheets("SheetName").Shapes("ButtonName").TopLeftCe ll.address
If you made the button using the controls toolbox it is:
Sheets("SheetName").CommandButtonName.TopLeftCell. address
(just substitute in the correct names for your worksheet and the button)

"glpabr" wrote:


Hi

I have a spreadsheet with macros assigned to command buttons that
hide/unhide rows.....i also have a watched range (actually 2 ranges
joined by a union) that colours cells depending on the value...

What I want to be able to do is add a row or column and have the range
extended automatically .....is this actually possible....

Also, the command button works by call a function (passing the row
location as a variable) which calculates the next 5 lines and hides
them...see the code below:

Sub hideunhide(cellloc)

Application.ScreenUpdating = False
Dim cellstart, cellend As Long
cellstart = cellloc + 1
cellend = cellloc + 5

Dim RangeSelect As Range
Set RangeSelect = Range("b" & cellstart, "b" & cellend)
RangeSelect.Select

If Selection.EntireRow.Hidden = False Then
Selection.EntireRow.Hidden = True
Range("a" & cellstart).Select
Application.ScreenUpdating = True

Else
RangeSelect.Select
Selection.EntireRow.Hidden = False
Range("a" & cellstart).Select
Application.ScreenUpdating = True

End If

End Sub

The only problem is that I have to manually enter the "cellloc" when
calling the function i.e

Private Sub CommandButton1_Click()
hideunhide (5)
End Sub

This is fine but I literally have hundreds of buttons and when I have
to insert one row it throws everything out from that line on...

I am obviously a novice to Excel and VBA so please don't punish me!!
Any suggestions on how I should go about it are welcome :-)

one last thing...is there a way of using VBA to find out what cell or
line a button is located??? either through it's properties or
otherwise...this would go a long way to solving my problem!!

Thanks
Glenn


--
glpabr
------------------------------------------------------------------------
glpabr's Profile: http://www.excelforum.com/member.php...o&userid=23967
View this thread: http://www.excelforum.com/showthread...hreadid=375894