I have a form (userform) which has a combo box and this combo box functions as a selector. I mean, when the person chooses one of the items (sheet1, sheet2, sheet3, etc.) inside the combo box, it views the sheet he/she picked. How will I do that thing? What code/s will I write in my module?
View 4 RepliesI have 25 sheets in the workbook and a combobox on the main page, The combobox references a range of 1-25 that represents the 25 hidden pages. right now i can get the sheets to unhide one at a time based on the selection e.g. combobox option 1 will unhide sheet 1 but the sheet are representing sites in a design so i need to have the option to select multiple sites in the combobox option so for example if i select 5 then sheets 1-5 should unhide. I hope I've explained that clearly.
The other question or option would be to just duplicate sheet 1 based on the combobox selection e.g. selection 5 duplicates sheet 1 5 times.
in the WorkSheet_Activate for Sheet 3, which changes the sheet name to the ComboBox Value. Is it possible to use the default name of the Sheet, in this case Sheet 3, if the ComboBox Value = "0". I have the following codes for a ComboBox & Userform:
Private Sub ComboBox1_Change()
Sheet3. Range("E1").Value = ComboBox1.Value
End Sub
&
Private Sub Userform Initialize()
ComboBox1.AddItem 0
For x = 1 To 60 Step 1
ComboBox1.AddItem Format(x, "0")
Next x
ComboBox1.Value = Sheet3.Range("E1").Value
I also have this code ..............
I have a name list on sheet2, column A that will have names added to and removed from. Everytime this list is modified it sorts in ascending order and creates a new sheet named after the name added. These items are added to an ActiveX combo box on sheet1. I need to know how to link the item in the combo box to the corresponding sheet(ex: item: "Jim", sheet name: "Jim").
View 2 Replies View RelatedThat particular question has been solved, but now i need it to work with multiple values from combobox.
So for example,
if "Master" is selected in combobox3, it will delete sheets 7, 8
if "CSR" is selected in combobox3, it will delete sheets 1, 8
if "Original IND" is slected in combobox, it will delete sheets 1,7
The current code will work when "master" is selected, however i am getting compile errors when i select "CSR" or "Original IND"
WhenI select CSR, it does create a new wb for me (without sheets 1,8) correctly but i get a Run time error.
Run-time error '-2147417848 (80010108)': Automation error
The object invoked has disconnected from its clients.
When Original IND is selected, i get a compile error and it highlights 'Make the new workbook active newWBK.Activate
Problem- I need a combobox on sheet1 to reference a column in sheet2 and return only a distinct list.
[Code].....
If I put my sheet2 column info on sheet1, and then reference it appropriately as built in the code above, it code works great. So I know it's close. I cannot change it to work with 2 sheets though. I've tried to "set wsSheet2 = wbBook.Worksheets("Sheet2")" and call it 'where I think it's appropriate', which is the problem. I don't know what's appropriate.
changing the steps in the code above, where in my case the combobox is on sheet1, and the range/data referenced is on sheet2?
This is what I've changed the original VBA to, and when I run the debug, it picks up the last "with...combobox1" statement as the error (error 1004, app/object defined error):
[Code] ......
I am currently using this code to place names in a combobox (active X). How can I use an array to perform each line to all 12 months without repeating the code over and over?Here is what I have:
Code:
Sub auto_Open()
Sheets("FEB").ComboBox1.List = Array("ALL", "ACT", "ROF", "MM")
Sheets("FEB").ComboBox2.List = Array("ACTvsROF_2", "ACTvsPLN_2", "ACTvsLY_2", "ROFvsMM_2", "ROFvsLM_2", [code].....
Sub ComboBox1_Chg()
For Each Sheet In Worksheets
If Sheet. Name <> "CoverPage" And Sheet.Name <> Sheets("CoverPage").ComboBox1 Then
Sheet.Visible = False
Else: Sheet.Visible = True
End If
Next Sheet
End Sub
It works if I step through it (F8) but the ComboBox doesn't work. It's named ComboBox1, and in the properties the ListFillRange shows all of the names in the list in the ComboBox correctly.
I have a formula that I can't get to paste successfully in the forum - it keeps getting cut off?!? ... but I think I can probably simplify my explanation to get the answer I want anyway.
I need to only show the value from AUS!$H$2:$H$17 if the C2 & D2 combination are the same as the AUS!$B$2:AUS!$B$17 & $AUS!$C$2:AUS!$C$17 combination.
I have a workbook that contains approx 50 sheets and will grow to somewhere in the region of 200.
The majority of sheets, which contain the raw data referenced by the renaining sheets, are hidden. I will occasionally need to update the data in some of those hidden sheets and would like an easy / quick way of unhiding them.
The front page has several comboboxes which select the page needed for the calculation being performed, eg I select Chapter2 in the first combobox, section 4 in the second and page 12 in the 3rd. The output is combined / abbreviated into into a cell eg Ch2-Sec4-P12. That being the name of a sheet I then use INDIRECTs to retrieve the data I want and place it in a spare sheet, that works well.
I'd like to do the same to select the sheet to unhide. I can setup the comboboxes to give the name of the sheet I want to unhide / hide in a cell but then I'm stuck;
How can I use the contents of a cell in place of the sheet name in a macro command such as Sheets("data").Visible = Not (Sheets("data").Visible) ?
I have set of user-form contains with Combox & 2 textbox and to generate report one cmd button
I have 3 different sheet contains report of daily activities ( Dispatch,Closed,Cancel)
If Dispatchcalls Select In Combobox1 ,Then Filter Start And End Date In Two Textboxes Then Click Cmd" Export Data To Excel"Extract Data from dispatchcalls Then Save Data Into Excel File As "Dispatchcalls".
If Closedcalls Select In Combobox1 Then Filter Start And End Date In Two Textboxes Then Click Cmd" Export Data To Excel"Extract Data from Closedcalls Save Data Into Excel File As "Closedcalls".
If Cancelcalls Select In Combobox1 Then Filter Start And End Date In Two Textboxes Then Click Cmd" Export Data To Excel"Extract Data from Cancelcalls Save Data Into Excel File As "Cancelcalls".
"C:UsersmaniDesktopNew folderLenvo_ReportsONSITE CasesVlokupuf" This is path i stored existing 3 file dispath,closed,cancel
Am trying to get dynamic population of 2nd combobox based on match from criteria in combobox 1.
if column a = bears and column b = colours of bears then
when I select bears in combobox one, combobox 2 would populate with colors of bear.
I am think of having a combobox 1 change event that evaluates each row in a specific range (does it match the criteria?) if so, then add 2nd cell (column b) of that row to the combobox 2.
I know it would probably involve match and offset, add item and loop, but I am not sure what the syntax is.
I need to populate two combo boxes from excel sheet, the data will be like below:
Column A Column B
A 1
A 2
A 3
A 4
A 5
B 100
B 101
B 102
So from the above data, one combo box should hold unique values A & B.
On selecting a value from the 1st combo box A or B, respective values should be populated in 2nd combo box.
So the data should be like below:
If A is selected in the 1st combo box, then 2nd combo box should only show the values 1,2,3,4 & 5.
If B is selected in the 1st combo box, then 2nd combo box should only show the values 100,101 & 102.
Friends I need it in a macro and one important point is, this is dynamic and it is not static and the data can be more.
When I make a comboBox selection from the dropdown menu, the menu stays down until the last statement of the comboBox code is finished. How do I make the dropdown disappear immediately after the selection is made?
View 9 Replies View Relatedtry and achive is when a user selects a item from the 1st Combobox the 2nd Combobox is the populated with the cell that is to the right of the selected item.
for Example if a user selects AAB from combobox1,, Combobox2 should populate with Belly.
Maby using combobox1_Exit for the excercise.
I created a UserForm then linked ComboBox1 to range A2:A, TextBox2 to range E2:E, and ComboBox3 to range M2:M of the same worksheet, named Sheet3. The row contents in Column A, Column E, and Column M are associated. Therefore, when the UserForm is active I want to be able to select a row from Column A in ComboBox1 and have the UserForm pull the contents from the same row of Column E into TextBox2, and Column M into ComboBox3. Here is what I have so far, but its not quite doing it.
Code:
Private Sub UserForm_Initialize()
Sheets("Sheet3").Activate
Dim ColARange As Range
[Code].....
I have a table, headers "FirstName" and "SurName".
Further a Userform with 2 Comboboxes "FirstName" and "SurName"
I'd like to choose the FirstName (say Jack) in the "FirstName" combobox, and based on that get the choice of the Surnames of all my Jacks in the "SurName" combobox.
Actually my sheet has much more fields and comboboxes, but i think my problem is just that I do not find a way to populate them dynamically.
How can i Loop through a combobox's values and compare to a string value and then set the listindex of the combobox to that value?
View 5 Replies View RelatedI have 2 cells with numbers. In a 3rd cell I want to create a formula which looks at the 2 data cells and shows a value. The rules are the following: If C1 or C2 are bigger than Xthen C3=value1 else C3=value2. I have some basic excel knowledge but im not very familiar with functions. I'm using Excel 2007.
View 4 Replies View RelatedI need to consolidate several amount and make sure which of them falls into a sum of an amount.
I wonder if excel can find all the calculation in range A, and highlight those that equal to the cell D10 (example as per my attachment).
Calculation.xlsx
how to create a list (I know how to calulate the count) of combinations without repetition when choosing 2,3,4 and 5 words from a set of 5 in Excel 2007.
e.g. Alpha,Bravo,Charlie,Delta,End
(AlphaBravo=BravoAlpha)
Choosing 2 = 10
Choosing 3 = 10
Choosing 4 = 5
Choosing 5 = 1
I have data in a 5x5 area and I would like a VB Script or Function that can give every possible combination of one number from each column added together.
i.e.
500 400 300 200 100 1500
400 300 200 100 500 1900
300 200 100 500 400 1800
200 100 500 400 300 etc...
100 500 400 300 200
I have a list of numbers from 1-7 would look like this each number in a seperate cell.
3 5
1 2 3 5 6 7
2 4 5 6 7
6 7
4 6
6
I want to use one number from each row (which there is only 6 rows) and then find every number from 1-7 that will complete the sequence 1-7. So with the numbers above (using one number from each row) the only other numbers that could be used would be 3 or 5.
The combos that would work:
row 1 use 3 ----------- row 1 use 5
row 2 use 1 ----------- row 2 use 1
row 3 use 2 ----------- row 3 use 2
row 4 use 7 ----------- row 4 use 7
row 5 use 4 ----------- row 5 use 4
row 6 use 6 ---------- row 6 use 6
5 would complete ---------- 3 would complete
Remember the numbers and how many numbers in each row can change but will always be 1-7 and I always need to find every number that can complete the sequence 1-7 by using one number from each row.
on combination of numbers
on the extreme left column, i have 23 numbers from A1:A23. All 23 numbers are in the form of 4 digit. For example A1 there is 1234, i need to display the possible 3 digit combination of this in the same row (like say 123,124,234,134 in B1,C1,D1 AND E1).
Another example in A2 there is 3545, i need to display 354,455,355 in the same row in B2,C2,D2
I need to perform this operation for the 23 numbers on the extreme left row. Can give me some hint on the code.
I am trying to show the all possible combinations of a set of numbers in Excel, in my case I think permutations are more appropriate to use. For example: there are three numbers 1, 2, 3 I want to show results like:
1, 2, 3
1, 3, 2
2, 1, 3
2, 3, 1
3, 2, 1
3, 1, 2
The functions in Excel available only give the total number, but I want to see these combinations!
I was just wondering if it was possible to only allow cells in a worksheet to only allow values that are a combination of 2 arrays whether it be through data validation or other means. For example, if I have an array that has a b and c and a second one with 1 2 and 3, is it possible to only allow values a1 a2 a3 b1 b2 b3 c1 c2 c3?
View 2 Replies View RelatedI have set of people where by based on Level & designation want to find the percentile on their CTC.
Attached is the sample file. Percentile.xlsx
Can Offset be combined in an Index Match Match formula as per the attached sample?
View 4 Replies View RelatedI'm trying to use a combination of Hlookup and COUNTIF. I'm selecting a date value in a cell using data validation. I'm then wanting to write a formula to lookup that value in a row of dates, and then use a countif to find all the '1' values in that column.
View 6 Replies View RelatedIve attached my example but explaination of what i am trying to do is below:
In sheet 1 i have products listed with a product ref
In sheet 2 i have a list of features by product ref.
I want to be able to put each feature next to the relevant product in sheet 1, some products may have 3 features, others may have 5 or more.