VBA Combobox values

[Legacy 397974]

Hi All!

I am using the following macro (Create_MIS) to generate the report based on the values entered into the Inputbox. Everything works fine, but I wanted to play slightly with adding the Combox, which will allow user to select those values from the list and after hitting the button, the report will be generated automaticaly.

Now, the problem, which I am struggling with is connected with the proper linkage of Combobox values and my code. I was trying the below reference, but it does not seems to be a proper way:

col = UserForm1.ComboBox1.Value
strName = UserForm1.ComboBox2.Value

I 'd appreciate some help, as I've never worked with the VBA objects before.

Full code:

Sub Create_MIS()

Dim Wb As Workbook, NewWb As Workbook, Ws As Worksheet, cfind As Range, strName As String, col As String
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim ComboBox1 As Object
Dim ComboBox2 As Object
Dim comboValue As String
Dim UserForm1 As Object

Set Wb = ThisWorkbook
Set NewWb = Workbooks.Add

'col = InputBox("Select column name [L]")
'strName = InputBox("Enter business name")
col = UserForm1.ComboBox1.Value
strName = UserForm1.ComboBox2.Value

For Each Ws In Wb.Sheets(Array("Sheet1", "Sheet10")) '<-- change sheet names as needed
    With Ws.Range("A1", Ws.Cells(1, Ws.Columns.Count).End(xlToLeft))
        If .Parent.AutoFilterMode Then .Parent.AutoFilter.ShowAllData
        Set cfind = .Find(what:=col, LookIn:=xlValues, lookat:=xlWhole)
        If Not cfind Is Nothing Then '<-- if the header has been found
            .AutoFilter Field:=cfind.Column, Criteria1:="*" & strName & "*" '<-- filter all columns of the referenced row cells
            .Parent.UsedRange.Cells.SpecialCells(12).Copy NewWb.Sheets(NewWb.Sheets.Count).[A1]
             NewWb.Sheets.Add After:=NewWb.Sheets(NewWb.Sheets.Count)
        End If
    End With
Next

With Application
    .DisplayAlerts = False
        NewWb.Sheets(NewWb.Sheets.Count).Delete
    .DisplayAlerts = True
        NewWb.SaveAs Filename:="Metrics" & " " & InputBox("Enter business name")
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

Thanks!

 

[dmt32]

Thank you very much Dave, it worked now!

Glad we got there in the end

Now all working, May want to consider users closing the form by pressing the X in right hand corner

You can do all this very simply by including following code in your forms code page

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'stop exit via X
    Cancel = CloseMode = 0
End Sub

This will disable exit by pressing X

Dave

 

[Legacy 397974]

Many thanks for the tip! It will for sure make a nice addition to the UserForm.

Again thanks for help!