Using ScrollBar to hide groups of columns

[jbrojbro]

Hi all. I'm pretty new to the VBA game. I've got a large spreadsheet with many cells that need user input. A bunch of calculations are performed on subsequent worksheets, but I want the user to only see the fields they need to enter. I'm trying to write some code that will use a scrollbar (form control, not activex) to only show one group of columns at a time. For example, if ScrollBar1.Value = 1, then show columns "A:D" and hide colums "E:Z". If ScrollBar1.Value = 2, show colums "E:H" but hide columns "A:D" and "I:Z", etc.

Here's the code I'm trying to get going, but I keep getting various errors when I try to execute.

Private Sub ScrollBar1_Change()
  Dim v As Integer
  v = ScrollBar1.Value
  Columns("A:Z").Select
  Columns("A:Z").EntireColumn.Hidden = True
  Select Case v
    Case 1
      Columns("A:D").Select
      Columns("A:D").EntireColumn.Hidden = False
    Case 2
      Columns("E:H").Select
      Columns("E:H").EntireColumn.Hidden = False
    Case 3
      Columns("I:L").Select
      Columns("I:L").EntireColumn.Hidden = False
  End Select
End Sub
Private Sub ScrollBar1_Scroll()
  ScrollBar1_Change
End Sub

As it stands now, when I click the scrollbar I get the error: "Compile error. Method or data member not found," and the ".Value" in Line 3 is highlighted. Is there something obvious that I'm doing wrong?

Thanks!

 

[jbrojbro]

In case this is helpful to anyone in the future, I ended up just using the value of the cell to which the scrollbar was linked as the Select/Case variable.

So, v = Range("$B$2").Value

I still think it's weird that it didn't work the other way, but oh well!

 

[Peter_SSs]

The code you posted looks like it should belong to an ActiveX ScrollBar not a Forms ScrollBar.
Using an activeX ScrollBar called ScrollBar1, your code worked as is for me.

Also, since your column groups seem consistent (groups of 4) I have a suggestion to replace all the different case statements.
You could try this in a copy of your workbook.

1. Insert an ActiveX ScrollBar

2. Right click the ScrollBar and choose Properties

3. (Sorry, deleted this step)

4. Set the Max property to say 6 (6 groups of 4 columns gives 24 columns A:X)

5. Set the MIn property to 0 (if 0 is later chosen, all columns will be made visible (or could be hidden if you wanted)

6. Close the Properties window

7. Right click the ScrollBar and choose View Code. This should create thes two lines of code, though the name could possibly be different

Private Sub ScrollBar1_Change()

End Sub

8. Paste the following code between the above two lines of code. Make sure the red bit matches the actual ScrollBar name.

  Dim c As Long

  c = ScrollBar1.Value * 4 - 3
  If c = -3 Then
    Columns("A:Z").EntireColumn.Hidden = False
  Else
    Columns("A:Z").EntireColumn.Hidden = True
    Columns(c).Resize(, 4).Hidden = False
    ActiveWindow.ScrollColumn = c
  End If

9. Back at the worksheet exit Design Mode by clicking that button on the Developer tab.

Try using the ScrollBar. As well as simplifying the code (assuming you do have all equal size column groups) this also ensures the unhidden columns are actually visible on the screen without having to scroll left.

 

[jbrojbro]

Thanks Peter, I appreciate your input. You were correct, I was using a Forms scrollbar instead of the ActriveX scrollbar - good catch. The consistency of 4 columns in my example was just an example, and the chunks of columns are indeed variable in number, but I'll keep your code should it come in handy down the road. Now I'm about to post my next ScrollBar-related question for the group!

JBro

 

[Peter_SSs]

The consistency of 4 columns in my example was just an example, and the chunks of columns are indeed variable in number,

So here is a more compact way to deal with the various cases that you may wish to consider for the future. Note that there is no need to select the columns to hide/unhide them and that I have only had to write the unhide code once - after the Select Case section. The Case 0 section isn't really required in this case but is included for completeness.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ScrollBar1_Change()<br>  <SPAN style="color:#00007F">Dim</SPAN> VisCols <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>  <br>  <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> ScrollBar1.Value<br>    <SPAN style="color:#00007F">Case</SPAN> 0: VisCols = "A:Z"<br>    <SPAN style="color:#00007F">Case</SPAN> 1: VisCols = "A:D"<br>    <SPAN style="color:#00007F">Case</SPAN> 2: VisCols = "E:F"<br>    <SPAN style="color:#00007F">Case</SPAN> 3: VisCols = "G:K"<br>    <br>    <SPAN style="color:#007F00">'Add more Cases as required</SPAN><br>    <br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>  Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>  Columns("A:Z").Hidden = <SPAN style="color:#00007F">True</SPAN><br>  <SPAN style="color:#00007F">With</SPAN> Columns(VisCols)<br>    .Hidden = <SPAN style="color:#00007F">False</SPAN><br>    ActiveWindow.ScrollColumn = .Column<br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>  Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>