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

Eurekaonide

Active Member
Joined
Feb 1, 2010
Messages
387
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,264
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,264
Office Version
  1. 2013
Platform
  1. Windows
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
VBA Code:
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
 
Solution

Eurekaonide

Active Member
Joined
Feb 1, 2010
Messages
387
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,264
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Feb 1, 2010
Messages
387
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,264
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Feb 1, 2010
Messages
387
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. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,353
Messages
5,601,136
Members
414,430
Latest member
jtdinh205

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top