Excel vba command button code (userform)

[tariq096]

When I click a location and then the “ok” command button I want it display values from the entire column of the location I selected . can someone please help me figure out what is the code to do this ?

 

[davesexcel]

Where do you want it displayed?

 

[tariq096]

when I select the location and click ok I want it displayed on the same worksheet. Thank you for responding . I need to figure out the code

 

[davesexcel]

According to the image, it is already displaying on the sheet.

 

[tariq096]

For example if I click a specific location and then hit the “ok” button , I just want to see the entire column as well as the years in the first column displayed side by side

 

[tariq096]

i know but I have to use a vba component in my project. So for example if I click “Acre” which is a location and then hit the ok button , I would like to see the years in the first column (1988-2013) and the values for each year in the column acre side by side . having difficulty coming up with this code . Any help will be greatly appreciated

 

[tariq096]

can someone who knows code please help

 

[Fluff]

Create a new class module & call it ClsOptBtn then put this code in it.

Option Explicit
Public WithEvents Optbtn As MSForms.OptionButton
Private Sub OptBtn_Click()
    Dim Fnd As Range
    Set Fnd = Range("2:2").Find(Optbtn.Caption, , , xlWhole, , , False, , False)
    If Not Fnd Is Nothing Then
        Cells.EntireColumn.Hidden = False
        If Fnd.Column > 2 Then Range("B1", Cells(1, Fnd.Column - 1)).EntireColumn.Hidden = True
    End If
End Sub

Then in the UserForm module put

Private OptBtnEvent As Collection

Private Sub UserForm_Initialize()
    Dim Ctrl As MSForms.Control
    Dim Obtn As ClsOptBtn

    Set OptBtnEvent = New Collection
    For Each Ctrl In Me.Controls
        If TypeOf Ctrl Is MSForms.OptionButton Then
            Set Obtn = New ClsOptBtn
            Set Obtn.Optbtn = Ctrl
            OptBtnEvent.Add Obtn
        End If
    Next
End Sub

The line in blue must be at the very top of the module, before any code.
When you click an option button, it will hide the relevant columns