Obtain Data from a form Combobox and then use it in the rest of the VBA Code

[Eurekaonide]

Hi All

I wan to load up a simple combobox in a form - the data in the drop down comes from a named range in my workbook elsewhere. I can get the form to load etc, but I want to identify the item chosen by the user and then use that to find all the rows of data that equal that data set.

For example if they select that they have brown hair, in my data set I want the column which states hair colour to delete all rows out completely that do not equal brown hair, so that the data set that is left is all those with brown hair.

Can anyone suggest to me the best code to use for this please?

Thank you

 

[My Aswer Is This]

So would you please give me these specific details:
1. The name of the ComboBox
2. The name of the sheet we need to search and delete rows from
3. In what column on the search sheet will we search. Give answer like sheet named "Alpha" column "G"
With these details I should be able to help you

 

[My Aswer Is This]

This script assumes:
1. The name of the Combobox is ComboBox1
2.The name of the search sheet is "Alpha"
3. Column of the sheet you want to search is Column B

Private Sub ComboBox1_Change()
'Modified  11/29/2020  4:36:40 AM  EST
Dim ans As String
ans = ComboBox1.Value
With Sheets("Alpha")
   .Range("B1").AutoFilter Field:=1, Criteria1:="<>" & ans
     .AutoFilter.Range.Offset(1).EntireRow.Delete
    .Range("B1").AutoFilter
  End With
  MsgBox "End"
End Sub

 

[Eurekaonide]

Thank you so much "My Answer is this" - one query I am having is on the combo box I have created this in a custom form with a cancel button which literally just unloads and ends but the other button how do I get this to refer back to the original sub().???
Or am I going about it incorrectly?
I have
Sub Run_Code()
'this then checks the data in sheet 1 Is all calculating correctly then call up Combobox1

' Combobox1 then finds out which workstream in column B they want to show, then I was expecting the "OK" button to send you back to the original SUB Run_Code but should it just do the above work and end like that? maybe it doesn't need to go back to the original sub?

Your help is much appreciated thank you.

 

[My Aswer Is This]

My first question is:
You said:
I have created this in a custom form

Do you mean a UserForm?

If not what is a Custom form?

And I do not understand any of this:
You said:
with a cancel button which literally just unloads and ends but the other button how do I get this to refer back to the original sub().???
Refers back to what original sub?

I think you need to explain all in specific detail.
Like in my original post I mentioned you did not give sheet name of column to look in.

 

[Eurekaonide]

Hi
1. The name of the ComboBox - Combobox1
2. The name of the sheet we need to search and delete rows from - ("Static Report")
3. In what column on the search sheet will we search. Give answer like sheet named "Alpha" column "G" - Sheet Name is "Static Report" column is "B"

I have created a userform1 and put a combox1 in it and 2 buttons - one is to cancel and one is to carry on with the code.

I have a macro or SUB Sort_Report()
'as this is ensuring all the data is formatted correctly first and formulas are all working, then it calls up the Userform1 so that the user can select from the combobox1 which element we want to show only.
Upon the selection in the combo box I want to filter on everything except for that so I can delete them all out and just leave the selection the user asked for.

My query is, if the code is populated in the userform "OK" button to filter and delete the rows will I need to go back to the SUB Sort_Report() and if so how? I'm thinking I do not need to go back to it though.

Thanks for your help and patience its appreciated.

 

[My Aswer Is This]

So the code I provided should do all you asked for if you just change the sheet name.
I see no need to go back to the sort script.
But you can call a sub routine by using.
Call Sort_Report

 

[Eurekaonide]

So the code I provided should do all you asked for if you just change the sheet name.
I see no need to go back to the sort script.
But you can call a sub routine by using.
Call Sort_Report

Thank you so very much for all your help it is much appreciated.

 

[My Aswer Is This]

Thank you so very much for all your help it is much appreciated.

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.