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!

 

[Fluff]

In what way is you code not working?

 

[dmt32]

Hi,
if you are unloading your userform from memory then controls will lose their values

you could try following & see if helps you

In your Userform the line of code you have to unload which I am guessing looks like below

Unload UserForm1

change it to this

Me.Hide

Try following updates shown in bold to your 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

'***********DELETE THESE DECLARATIONS***********
'Dim ComboBox1 As Object
'Dim ComboBox2 As Object
'Dim comboValue As String
'Dim UserForm1 As Object
'************************************************

Set Wb = ThisWorkbook
Set NewWb = Workbooks.Add


    With UserForm1
        .Show
    col = .ComboBox1.Value
    strName = .ComboBox2.Value
    End With

    Unload UserForm1
   
    'rest of your code

Bit of a guess but Hope Helpful

Dave

 

[Legacy 397974]

Hi Dave,

Many thanks for your help.

I've change the unload command in my code, so it look like that:

Private Sub CommandButton2_Click()
Me.Hide
End Sub

Now, when I try to run the macro, I receive and error 'Object variable or With block variable not set' on this line:

    With UserForm1
        .Show

Any thoughts why is that?

 

[dmt32]

did you copy ALL of the code?

With UserForm1
        .Show
    col = .ComboBox1.Value
    strName = .ComboBox2.Value
    End With

    Unload UserForm1

Also, I just guessed that you main code is OUTSIDE your userform code in a standard module?

Dave

 

[Legacy 397974]

Yes, however I've just noticed that I forgot to remove the variables, which you had tagged with *. Sorry for that, my bad.

When, I try to run it now I get 'Object required' error at

[B] .Show[/B]

 

[dmt32]

post all the code you have in your userform

Dave

 

[Legacy 397974]

Hi, this is the code within Userform:

Private Sub CommandButton1_Click()

Call Create_MIS

End Sub

Private Sub CommandButton2_Click()

Me.Hide
End Sub

Private Sub UserForm_Initialize()

ComboBox1.Clear
ComboBox2.Clear
ComboBox1.List = Sheets(1).Range("A1:A3").Value
ComboBox2.List = Sheets(1).Range("b1:b3").Value

End Sub


Private Sub UserForm_Activate()

ComboBox1.List = Sheets(1).Range("A1:A3").Value
ComboBox2.List = Sheets(1).Range("b1:b3").Value

End Sub

and the one, which I use for opening the UserForm:

Sub DisplayUserForm()
    
    Dim form As New UserFormTest
    form.Show
    
End Sub

 

[dmt32]

DELETE ALL following codes

Private Sub UserForm_Activate()

ComboBox1.List = Sheets(1).Range("A1:A3").Value
ComboBox2.List = Sheets(1).Range("b1:b3").Value

End Sub

Private Sub CommandButton1_Click()

Call Create_MIS

End Sub

Sub DisplayUserForm()
  
    Dim form As New UserFormTest
    form.Show
  
End Sub

Now RUN the Create_MIS code (include my suggested changes) which I assume is in a standard module
The userform should display & when you dismiss it - the variables in your code should contain the values from the comboboxes

Dave

 

[Legacy 397974]

Thank you very much Dave, it worked now!