Combo Box Control with VBA

[marka87uk]

Hi,

Firstly, if Mr Excel himself reads this, then I'd like to say I really enjoy the Guerilla Data Analysis book!

Secondly, and on-topic, I wish to use a Combo Box to select columns to hide or show.

For instance, in columns C-F I have Period1, G-J, Period2, K-N, Period3, and so-on.

I wish to be able to select a period from the Combo Box, and then hide or show the columns as needed depending on the value.

I'm very new to VBA (but not totally new to VB), but could someone explain to me how to go about doing this?

I'm using Excel 2003, where I see there are two Combo Box's under the Forms and the Control Toolbox toolbars.

 

[marka87uk]

Well! I have to say, I cracked it myself!

I don't know if this is the best or most correct method, but feel free to advise me please!

Private Sub Workbook_Open()
CreatePeriods
End Sub

Sub CreatePeriods()
    With Sheet1.SelectPeriod
        .AddItem "Period 1"
        .AddItem "Period 2"
        .AddItem "Period 3"
    End With
End Sub

Private Sub SelectPeriod_Change()
Columns("C:N").Select
Selection.EntireColumn.Hidden = True

    If SelectPeriod.Value = "Period 1" Then
        Columns("C:F").Select
        Selection.EntireColumn.Hidden = False
    End If
    
    If SelectPeriod.Value = "Period 2" Then
        Columns("G:J").Select
        Selection.EntireColumn.Hidden = False
    End If
    
    If SelectPeriod.Value = "Period 3" Then
        Columns("K:N").Select
        Selection.EntireColumn.Hidden = False
    End If
End Sub

It works exactly how I wanted!

 

[Norie]

Mark

Glad to see you've got it working yourself.

One point though, you don't need to select.

Private Sub SelectPeriod_Change()
    Columns("C:N").EntireColumn.Hidden = True

    Set rng = Columns("C:F")
    
    rng.Offset(, SelectPeriod.ListIndex * 4).EntireColumn.Hidden = False
    
End Sub

 

[marka87uk]

Ah, that simplifies it a bit! Thanks

Out of interest, what does the variable you used, rng, stand for?

 

[Norie]

It doesn't really 'stand' for anything.

What I'm doing is creating a reference to an object, specifically the range object columns C:F.

I really should have declared the variable like this.

Dim rng As Range

 

[marka87uk]

Ah I see, it's just for range! Thanks, I'll have a further play with this!