Combo Box command other sheets

[davidhall80]

I am try to make my combo Box Carry out actions on other sheets. When I get to the "Range("a1").select, it craps out. Is it possible for a combo box to make references to other sheets



Private Sub ComboBox1_Change()


If ComboBox1.Value = 1 Then
Rows("7:20").Select
Selection.EntireRow.Hidden = True
Rows("10:15").Select
Selection.EntireRow.Hidden = False
Range("C14").Select
Sheets("sheet2").Select

Range("a1").Select


Selection.EntireRow.Hidden = True
Rows("10:15").Select
Selection.EntireRow.Hidden = False
Range("C14").Select

End If



End Sub

 

[DominicB]

Good afternoon davidhall80

Some of the ActiveX controls struggle when you're trying to access other sheets. A workaround is to place the code into a regular module, and have just one line in your combobox module calling it :

Private Sub ComboBox1_Change() 
Call Macro1
End Sub

HTH

DominicB

 

[davidhall80]

So what your saying is to tell the combo box code to run a macro containing the code i want instead of placing the actual code in the Combo Box code inself

 

[Norie]

David

There is normally no need to select anything.

What exactly are you trying to do?

What sheet is the button on and what sheet are the rows you want to hide/unhide on?

 

[davidhall80]

The first sheet is my main sheet. It pulls from other sheets in the workbook. If I choose New York in the Combo Box. I want to see the New Section of that sheet...this is not a problem. However...on the other tabs across the workbook, I also need only the New York section to show so I can input values that the Main Sheet will pull. does this make sense or am I saying it wrong. What I'd like to do is only have one input Box on my main sheet and not have input boxes on all the sheets where I have to choose New York 15 times. Is this possible.

 

[erik.van.geit]

Hi, David,

it is clear form your code what you are trying to do and which sheets you want to change

you can easily do operations on other sheets, but you need to reference them

in "normal" modules code like
Range("A1") is considered as ActiveSheet.Range....

in sheetmodules
Range("A1") refers to the sheet where code belongs to
in some way you could write "thissheet".Range...
this way exists, you could use Me.Range...

hope this is clear
you don't need to select nor the sheet nor the cells, just take care of references

If ComboBox1.Value = 1 Then
Rows("7:20").EntireRow.Hidden = True
Rows("10:15").EntireRow.Hidden = False
Range("C14").Select
    With Sheets("sheet2")
    .Range("a1").EntireRow.Hidden = True
    .Rows("10:15").EntireRow.Hidden = False
    End With
End If

kind regards,
Erik

 

[erik.van.geit]

The first sheet is my main sheet. It pulls from other sheets in the workbook. If I choose New York in the Combo Box. I want to see the New Section of that sheet...this is not a problem. However...on the other tabs across the workbook, I also need only the New York section to show so I can input values that the Main Sheet will pull. does this make sense or am I saying it wrong. What I'd like to do is only have one input Box on my main sheet and not have input boxes on all the sheets where I have to choose New York 15 times. Is this possible.

this might be useful for you

ASSUMPTIONS
1. you have a list with cities + rowreferences next to it (last rowreference may not have cityname before it)
2. those cities are the listfillrange of your combobox (in my example A2:A5) you can do this in hidden columns if you want
3. 2 controls on your sheet "combobox1" & "commandbutton1"
TABLE
  A        B        
1 city     startrow 
2 New York 10       
3 Brussel  16       
4 Paris    24       
5 Roma     28       
6          45       

Main Sheet
[Table-It] version 06 by Erik Van Geit

when clicking commandbutton all rows except row1 & the "cityrows" are hidden
example: choose Paris + click button: only rows 24 to 27 will be visible

Option Explicit

Private Sub CommandButton1_Click()
'Erik Van Geit
'060915
'hide rows accoring to comboboxreference
'itemlist has rowreferences in the next column (1 more then itemlist)

Dim sh As Worksheet
Dim SR As Long          'Start Row with city info
Dim ER As Long          'End Row with city info

SR = Range(ComboBox1.ListFillRange)(1, 2)(ComboBox1.ListIndex + 1)
ER = Range(ComboBox1.ListFillRange)(1, 2)(ComboBox1.ListIndex + 1)(2) - 1

Application.ScreenUpdating = False

    For Each sh In Sheets
        With sh
            If .Name <> Me.Name Then
            'do not hide all rows at once, you could get a bug 'cannot shift objects from sheet'
            'therefore it's safer to use three lines
            .Rows("2:" & SR - 1).EntireRow.Hidden = True
            .Rows(SR & ":" & ER).EntireRow.Hidden = False
            .Rows(ER + 1 & ":" & Rows.Count).EntireRow.Hidden = True
            End If
        End With
    Next sh
    
Application.ScreenUpdating = True

End Sub

best regards,
Erik

 

[davidhall80]

Thanks a bunch......your a genius !!!!!!

 

[erik.van.geit]

you're really very WELCOME

try to analise the lines to learn how things work

example

SR = Range(ComboBox1.ListFillRange)(1, 2)(ComboBox1.ListIndex + 1)

this part, you know, it's an address: in this case "A2:A5"
ComboBox1.ListFillRange
so
Range(ComboBox1.ListFillRange)
is refering to Range("A2:A5")
got it ?
then continue ...

check out what (1, 2) will do
Range("A1")(1, 2).Select
and see what happens when you do this
Range("A1")(1, 2)(1).Select
&
Range("A1")(1, 2)(2).Select
(this blue value has to be subsittuted by the listindex +1)
since "ListIndex" starts with "0" for the 1st item we need to add 1
ComboBox1.ListIndex + 1
to get the correct offset
this cell's value will be loaded in SR (Start Row)

resuming in plain english
find listrange's first item
go 1 to the left
go down according to listindex
retrieve value

cheers
have a nice weekend !
Erik

 

[davidhall80]

I've been studying the code all night. thank you very much for taking the time to explain it to me. i am very eager to learn visual basic. would you recommend any reading material for my personal growth?????