Limit replication of coding inside spreadsheet

[PaulBas]

Hi all, I'll start with the good news, in that I have managed to get all the VBA to work just how I want it to. My problem is that now every tie I need to edit the sheet or moving things round/add rows etc I need to make onerous modifications to the code!!

Basically I wasn't smart enough when I set up my coding at the start.

I have 30 checkboxes which control different rows. They are all sequential rows or groups of rows but no consistency in the number of rows in each group. I.e. 1-6 in one group, 7-10 in another 11-25 in the next etc.

I have used the following code to achieve what I was hoping for on my spread sheet:

Private Sub CheckBox1_Click()
'aminebloom checkbox
If Sheets("Sheet1").CheckBox1.Value = False Then
        Sheets("Sheet1").Rows("60:75").EntireRow.Hidden = True
      Else
         Sheets("Sheet1").Rows("60:75").EntireRow.Hidden = False
      End If
End Sub

As you can see each sub has the row numbers written in twice, which need to be edited twice every time something changes on my sheet.

Is there a way that I can pre-define the groups of rows I want in one place, then get the code of each of the 30 checkboxes to refer to that?

I'm confident that it can be done, just not sure what the best way of doing it is? I do appreciate that I'll have to modify the code of each of the 30 checkboxes, but hopefully after this any modifications will be simpler!!!!


Most appreciated of any advice or help that can be offered
Regards

Paul

[alansidman]

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

[alansidman]

Try this:

Private Sub CheckBox1_Click()
'aminebloom checkbox
Dim rng as range
Set rng = Range("A60:A75")
If Sheets("Sheet1").CheckBox1.Value = False Then
        Sheets("Sheet1").rng.EntireRow.Hidden = True
      Else
         Sheets("Sheet1").rng.EntireRow.Hidden = False
      End If
End Sub

or this:

Private Sub CheckBox1_Click()
'aminebloom checkbox
Dim lStart as long
Dim lEnd as long
lstart = Inputbox("What is your starting row")
lEnd = Inputbox("What is your end row")

Dim rng as range
Set rng = Range("A" & lStart & ":A" & lEnd)

If Sheets("Sheet1").CheckBox1.Value = False Then
        Sheets("Sheet1").rng.EntireRow.Hidden = True
      Else
         Sheets("Sheet1").rng.EntireRow.Hidden = False
      End If
End Sub

[PaulBas]

Firstly my apologies and thank you.

So if I modify the code thusly

Private Sub CheckBox1_Click()
'aminebloom checkbox
Dim rng1 as range
Set rng1 = Range("A60:A75")
If Sheets("Sheet1").CheckBox1.Value = False Then
        Sheets("Sheet1").rng1.EntireRow.Hidden = True
      Else
         Sheets("Sheet1").rng1.EntireRow.Hidden = False
      End If
End Sub

Can I collated all the

set rgn1 =  Range("A60:A75")
set rgn2 =  Range("A76:A80")
set rgn3 =  Range("A81:A95")

in one place, outside of each of the 30 Private Sub routines.

Is that possible?

Paul

[alansidman]

Set the ranges as Public outside the module. No need to set them inside the module.

Look at this link for an explanation.

http://www.ozgrid.com/forum/showthread.php?t=95488

[PaulBas]

I'm afraid we are hurtling towards my limit of VBA understanding and it frustrates me that I know this is barely elementary!!!

I have typed in the following code, but I get a complied error: invalid outside procedure. In particular it highlight the 'set' function. Is it obvious what I'm doing wrong??

Public rng1 As Range
Set rng1 = Range("60:281")
Public rng2 As Range
Set rng2 = Range("290:405")
Public rng3 As Range
Set rng3 = Range("414:438")
Public rng4 As Range
Set rng4 = Range("442:447")
Public rng5 As Range
Set rng5 = Range("451:459")
Public rng6 As Range
Set rng6 = Range("463:478")

Regards
P

[alansidman]

You have set the range without a reference to any column.

Try changing it up to look like this:

Public rng6 As Range
Set rng6 = Range("A463:A478")

It will not matter that you indicate a column since you are selecting the entire row to hide

[PaulBas]

I think that I'm still doing something wrong! I keep getting a complier error: Invalid outside procedure

this is the code that I have

Public rng1 As Range
Set rng1 = Range("A60:A281")
Public rng2 As Range
Set rng2 = Range("A290:A405")
Public rng3 As Range
Set rng3 = Range("A414:A438")
Public rng4 As Range
Set rng4 = Range("A442:A447")
Public rng5 As Range
Set rng5 = Range("A442:A447")
Public rng6 As Range
Set rng6 = Range("A463:A478")



Sub Auto_open()

'hide application questions
Sheets("Sheet1").rng1.EntireRow.Hidden = True
'hide product performance questions
Sheets("Sheet1").rng2.EntireRow.Hidden = True
'hide customer site section
Sheets("Sheet1").rng3.EntireRow.Hidden = True
'hide commercial section
Sheets("Sheet1").rng4.EntireRow.Hidden = True
'hide product quality section
Sheets("Sheet1").rng5.EntireRow.Hidden = True
'hide Technical Operations
Sheets("Sheet1").rng6.EntireRow.Hidden = True
'rest optionbuttons and site invest, Commercial, QC and TOD sections
Sheets("Sheet1").OptionButton1.Value = False
Sheets("Sheet1").OptionButton2.Value = False
Sheets("Sheet1").CheckBox27.Value = False
Sheets("Sheet1").CheckBox28.Value = False
Sheets("Sheet1").CheckBox29.Value = False
Sheets("Sheet1").CheckBox30.Value = False
        
End Sub

[alansidman]

Ok. My bad. You can't set the variable range as a constant. Instead here is a work around

Option Explicit

Dim rng As Range
---------------------------------------------------
Function Getrng() As Range
     Set Getrng = Range("A10:A20")
End Function
--------------------------------------------------
Sub Test()
Set rng = Getrng
rng.EntireRow.Hidden = True
End Sub

You will have to define (Dim) each of your ranges above the function
Create a Getrng1, Getrng2, etc for each of your ranges

Then in your Sub you can set each of the ranges so that you will only have to reset the GetrngX when you need to make a range change and not the Sub itself.

[PaulBas]

Hi alansidman,

so following your suggestion I now have the following code:

Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range
Dim rng6 As Range
---------------------------
Function Getrng1() As Range
     Set Getrng1 = Range("A60:A281")
End Function
-------------------------
Function Getrng2() As Range
     Set Getrng2 = Range("A290:A405")
End Function
------------------------
Function Getrng3() As Range
     Set Getrng3 = Range("A414:A438")
End Function
--------------------------------------
Function Getrng4() As Range
     Set Getrng4 = Range("A442:A447")
End Function
---------------------------
Function Getrng5() As Range
     Set Getrng5 = Range("A451:A459")
End Function
------------------------------
Function Getrng6() As Range
     Set Getrng6 = Range("A463:A478")
End Function
------------------------------------

Sub Auto_open()
Set rng1 = Getrng1
Set rng2 = Getrng2
Set rng3 = Getrng3
Set rng4 = Getrng4
Set rng5 = Getrng5
Set rng6 = Getrng6

'hide application questions
Sheets("Sheet1").rng1.EntireRow.Hidden = True
'hide product performance questions
Sheets("Sheet1").rng2.EntireRow.Hidden = True
'hide customer site section
Sheets("Sheet1").rng3.EntireRow.Hidden = True
'hide commercial section
Sheets("Sheet1").rng4.EntireRow.Hidden = True
'hide product quality section
Sheets("Sheet1").rng5.EntireRow.Hidden = True
'hide Technical Operations
Sheets("Sheet1").rng6.EntireRow.Hidden = True
'rest optionbuttons and site invest, Commercial, QC and TOD sections
Sheets("Sheet1").OptionButton1.Value = False
Sheets("Sheet1").OptionButton2.Value = False
Sheets("Sheet1").CheckBox27.Value = False
Sheets("Sheet1").CheckBox28.Value = False
Sheets("Sheet1").CheckBox29.Value = False
Sheets("Sheet1").CheckBox30.Value = False
        
End Sub

Unfortunately this doesn't work either. I get a runtime error '438' error. Debugging highlighst the follwing line: Sheets("Sheet1").rng1.EntireRow.Hidden = True

@Norie
the groups I want to define at the rows which are controlled by the checkboxes. Everything I'm trying to do is located on Sheet 1 of hte spreadsheet. I trust this answers your question?

Regards

Paul

[alansidman]

Let me do some testing and I will get back to you.

[PaulBas]

Thank you that would be great

[Norie]

Paul

How are the groups defined/identified?

Also, where are the checkboxes in location to the groups?