Call sub for another sheet + when sheets name is string in sheet

[Marianus]

Hi I

have problem
1., not working combo box, I dont know wha dont working

2., call sub with sheets name , I dont know syntax what I want to do


I want use button "Copy - call sub sorting + change year"

FILE : - callSub.vA004.xlsm

[tehneXus]

Hello,

what exactly isn't working? If you mean that the combobox is empty this is because it is filled every time the sheet is activated and the following event is triggered:

Private Sub Worksheet_Activate()

If you want the combobox to be filled when you open the workbook paste the following into the "ThisWorkbook" module:

Private Sub Workbook_Open()
    With ThisWorkbook.Worksheets("Report")
        With .ComboBox1
            .AddItem "2012"
            .AddItem "2013"
            .AddItem "2014"
        End With
        .ComboBox1.ListIndex = .Range("F1") - 1
    End With
End Sub

If there is sth else which isn't working please be so kind to explain the problem.

[Marianus]

thanks , one moment please I do new excel (use your answer ) great , thank you

syntax what working good

Application.Run Sheets("2012").CodeName & ".Sorting_Click"

I want this , but my syntax is not good

Name_sheets = Range("G1")

   Application.Run Sheets(Name_sheets).CodeName & ".Sorting_Click"

I want use button "Copy - call sub sorting + change year" (I want change sheet with combobox , name of worksheet is year )

[tehneXus]

try this in "CommandButton2_Click" on the "Report"-sheet

    Dim xlWs As Worksheet
    Set xlWs = ThisWorkbook.Worksheets(CStr(Range("G1").Value))
    Application.Run xlWs.CodeName & ".Sorting_Click"

[Marianus]

Its interesing problem , working only year is 2012

[Marianus]

Sorry . Im stupid I find error , my fault

this is working sheet , thank you for help

[tehneXus]

Hi,

the button "Copy - call sub sorting + change year" in this file: - callSub.vA004.xlsm it works for 2012 and 2013 for me

[Marianus]

HI ok thanks