2 Cascading Comboboxes, Works Only When On Sheet With The Data
Oct 23, 2009
I just started using vb two days ago so the problem is probably from the code. I created a userform with 2 comboboxes and 10 textboxes, when i choose an item from the first combobox it filters out the result from the second combobox.
And when i choose an item from the 2nd combobox it shows certain textboxes corresponding to that item. Everything works fine if i run the userform while on the sheet i pull the data off of (Devices sheet), but if i try it on another sheet then the comboboxes don't work anymore.
Another question i had is if there is simpler way to view the textboxes rather than the one i used in my code. And if there's a way to autoarrange the textboxes when i they are visible so that they all are in the same row.
View 5 Replies
ADVERTISEMENT
Jul 28, 2014
Is there a way to add cascading lists (from data validation or form/activex controls) to my excel spreadsheet WITHOUT using named ranges? Maybe structured references?
I need to avoid the named ranges because it will cause my workbook to have duplicate named range titles which I cannot avoid.
As a general example my issue arises because I have something like this where the titles are the same but they map to slightly different data. These also have to be cascading because the titles align to another list which I do not show in the example. I also considered using pivot tables, but the issue there is that the data validation lists repeat in the same worksheet. So I would have 3 cascading lists in row1 dependent on each other, but the same 3 lists in row2 dependent on row2 but not the previous row.
[Code] .....
View 3 Replies
View Related
Dec 30, 2008
I am running into an odd issue and hope someone might be able to shed some light.
I have created several lists on one sheet in a workbook and have assigned named ranges to each individual list. So far, so good.
On various other worksheets, I have created Data Validation rules to allow users to select the relevant data from drop-down lists. (using formulas such as =List1, =List2, etc. in the Source box to capture the named ranges I had created).
Everything works perfectly...EXCEPT for one worksheet, which continues to give me the error message, "You may not use references to other worksheets or workbooks for Data Validation criteria." Can anyone explain why this error would only occur on one worksheet and work fine for all the other sheets? I'm perplexed!
View 3 Replies
View Related
Feb 26, 2008
I am trying to use cascading Data validation, but I keep getting a ref error.
I have set up a Dynamic Named range called Claim_Type_1
This contains the following choices:
External_Meeting
Internal_Meeting
Attend_Training
I have then set up Dynamic Named ranges called "External_Meeting", "Internal_Meeting" and "Attend_Training"
On my sheet, I am using the following for the data validation:
Source formula "=INDIRECT(M7)" - M7 is the cell with the original validation
It then tells me "The source currently evaluates to an error"
View 9 Replies
View Related
Nov 9, 2009
I have 2 comboboxes in the complett worksheets. By selecting some values in this comboboxes, I need some code that will copy automatically some data from worksheets a and b, to worksheet export.
For example if I select "a" and "A26" in the comboboxes, I need range E26:B26( 4 col to the right of A26) from "a" worksheet, to be copied with Paste Special /Values/ Transpose into "export" worksheet in range A1:A50. Also I need in range A51 from worksheet "export" the value "A26" selected in the second combobox. Also in range A52 from worksheet "export" the value from range H40 from "complett" worksheet.
View 8 Replies
View Related
Feb 20, 2012
I Have a sheet with 4 activex comboboxes and 3 text boxes. If the right item is selected in the second combo box a user form opens up. That has 4 text boxes. It has a command button titled ok that takes the information from the 4 text boxes and puts them in a sheet called data.
Code:
Private Sub cmbOK_Click()
With Worksheets("Data").Range("A1")
.Offset(1, 8).Value = Me.txtFirm.Value
[Code]....
I need a way for the above code to run when the ok button on the user form is clicked.
View 7 Replies
View Related
Nov 30, 2006
If SheetExists(Sheets("data").Range("V" & x).Value) Then
'do Nothing
Else
Sheets.Add
ActiveSheet.Name = Sheets("Data").Range("V" & x).Value
End If
ProjectReview.Show
Public Function SheetExists(SName As String, _
Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function
I'm trying to add a new sheet, if a sheet with that name doesn't exist already.
Which I have a feeling is pretty simple!. My Error is occurring at the * in the code. Basically I am adding a sheet per record. I'm using a unique identifier for each tab. The first run through, it creates a new sheet and opens a user form. When I close out that form, it returns to here and fails to work the second time through. I'm fairly confident it's because I'm using a select statement, I've also tried using it this way, but the set statement is invalid because it's not refrencing an object....
View 2 Replies
View Related
Mar 31, 2014
In the following user defined function
Code:
Function CA2GT(ByVal S As String) As String
Dim x As Long, CA As Long, Total As Long
CA = InStr(1, S, "CA", vbTextCompare)
For x = CA To Len(S)
If Mid(S, x, 1) = "(" Then
'replace arguments: oldtext, start at number, number of characters, new text
[Code] .......
It works on one workbook but not another workbook. Why?
View 5 Replies
View Related
Jul 6, 2012
In one worksheet I have a large amount of data (around 300 columns and 1000 rows), growing bigger each day. This worksheet is called "datas_pH" and consists of column A with different categories, column B with different subcategories, and then the rest of the columns with the pH data.
In a different worksheet (called "database") I have 2 comboboxes where you should be able to select the categories and subcategories. For example, in the first combobox if you select A you should only be able to view and select 1, 2, 3, 4, 5. If you select B you should only be able to select 6, 7, 8, 9, 10. (I believe it may be called cascading comboboxes or something along those lines?).
Anyway, here's a code that works perfectly if the comboboxes and data are in the same data sheet:
Combo Box 1:
Code:
Private Sub ComboBox1_Change() Dim d1 As Object
Set d1 = CreateObject("Scripting.Dictionary")
i = 2 ' start at row 2
x = 1
rowValue = Cells(i, 1) ' set rowValue to the value of the first cell (top data row)
[Code] .....
How do I modify these codes so it will pick up data in the other worksheet ("datas_pH"). Combo Box 1 should pick up data from cells datas_pH!a:a, and Combo Box 2 should pick up from cells datas_pH!b:
View 3 Replies
View Related
Mar 5, 2009
I have this code attached to a button on the first sheet of a workbook with hundreds of sheets.
it is suposed to look for a cell that contains "SAY:" and then move one column to the right and make it a zero. It works on the first sheet but not on any other sheet.
View 7 Replies
View Related
Jan 27, 2010
Basically I want it to look similar to the vehicle search on auto trader, or whatcar, where you first chose the make, then use a second combobox for the model (dependent on the choice made in the first combobx.
View 6 Replies
View Related
Aug 20, 2007
My spreadsheet has approx 50 tabs, can I cascade them so that I don't have to scroll across all the time to find the one I want (ie, can they all be shown in the one window)
View 8 Replies
View Related
Aug 10, 2006
I have a worksheet with 13 columns. ....which i have attached.
I am trying to use cascading combobxes for priting the certaion rows that match the criteria in the 2 comboboxes.
I have -
Customer - in column C
Category - in column G
Complaint Owner - in column J
Month - in column L
Above 4 options are shown in the drop down list in Combobox 1.
Once user selects any one option from combobox1, combobox2 will populate with the relevent list of Customers, Categories, Complaint owners and months.
Also, depending the selection in combobox1, I want excel to search particular columns for the value in combobox2. And then display all the rows that fit the criteria in print preview (showing all the columns A:L, fit in one page)....which then can be printed by user.
I managed to the get the first part right where combobox2 populates based on selection in combobx1. But I am struglling to get the second part....
I have used a code that I found in another forum and tried to change it to suit my purpose, but obviously i have messed up the code and there are a lot of errors now...
View 4 Replies
View Related
Aug 21, 2013
I want to create a cascading combo box from some values that I have, ill explain how I approached it.
Cascading combo box is a combobox which values are obtained depending on the answer of a previous combo box.
So I set up a little table like so:
Categories Car Train Bike
Car Mercades First Honda
Train Ford Western Ducati
Bike Skoda Red Suzuki
and the combo boxes would be set aside from that.I collected the data in Named Ranges (A Range for Categories, A Range for Car, A Range for Train...)
The first data validation box contains = Categories the second data validation box contains =INDIRECT($C$2) (Where C2 was the range of the first validation box)
Once this was working, I decided to change the formula for the named ranges to allow me to add more values.
The formula was: =$F3$F8
I changed it to: =OFFSET($F$3,0,0,COUNTA($F3:$F96),1)
I expected this to work however, it just doesnt.... when clicking on the drop down arrow for the validation box, nothing happens. No box or values or dropdown.
View 2 Replies
View Related
Sep 8, 2009
I’ve created two combo boxes and what I seem to need are “cascading combo boxes”
I have named ranges on a worksheet called LookupLists
Column A has Areas
and Columns H to L has services within those areas.
Column headers, i.e. Row 1 of the columns, H to L are Serv1, Serv2 and so on.
Each area, in column A, has different services, in column H to L.
I have two combo boxes, what I’d like is depending on which Area is chosen in the first combo box, that the correct column is chosen which lists only those services available in that area; for example maybe Area A has services 2. Area B has services 4 …
… so in my “logical” mind if Combo box 1 I choose “Area B” then I want Combo box 2 to look at Column K and show the services from there..
View 9 Replies
View Related
Jun 4, 2013
I am having some trouble with cascading two tier drop down. I cannot seem to find out how to do it. I am trying to create a two tier cascading drop down in form control. I want the first drop down to be Products we have and the second one to be customers. I also want to be able to create a bar chart from these drop downs where after we choose our customer by product, it shows the revenue we have recieved from the customer by months. Is there a way to do this? I figured out how to do this with just one combo box, but I am unable to do a cascading one.
View 5 Replies
View Related
Aug 23, 2013
I have a spreadsheet with a VLOOKUP formula that I have to send to a customer weekly. They are required to type a code in column E of what they invoice, and it pulls the rate into column G based on a rate table I have. The formula is: VLOOKUP(E2,Rates!A:B,2,0)
When I made the spreadsheet, the formula worked perfect. But when they type their codes in and send it back to me, it's messed up. All of the codes they've typed in do pull the correct rate. However, if I try to go to column E and type a different code on top of what they typed (a code that IS IN the rate table), it will tell me: "The value you entered is not valid. The user has restricted values that can be entered into this cell".
If I create a new tab and mimic the exact formula and columns as I am trying to work with, it works perfectly. But for some reason, once they've typed their data into my spreadsheet, something messes up even though the formula is perfect, dragged down, etc. All columns are formatted as text. And the range in the rate table is correct as well. When I try to mimic the formula in the other tab, if I click a cell in column E, there is a drop down list there showing all the available codes from my rate table to choose from. But on the spreadsheet that is messed up has no drop down list on the cells.
View 1 Replies
View Related
May 21, 2009
I have attached a sample of the issue. The formula is found in cell B49. If I enter a gravity of 2.76 or higher in cell B47 I get #N/A in cell B49. Why will it not read any farther over than 2.75. I have been starring at this for awhile now. I might look pretty dumb after someone else looks at it.
View 2 Replies
View Related
Feb 10, 2010
I need to run a vlookup to find some data. But I have a lot of data about 600,000 lines. Currently this list is spread over several columns (as the limit is something like 50000). How can I make my reference range access multiple columns?
View 5 Replies
View Related
Jul 22, 2014
I'm thinking of writing a small tool to store user information on certain files.
The user data will be input by means of comboboxes on a userform.
Each combobox will need to contain a list of values previously used for that combobox. (I'm planning to use a separate ini file for each combobox)
All of the user data entered for a file will be saved into a single ini file.
Most of this is straightforward however I'm not good with comboboxes. And working this in with Ini files makes it harder!
I'm struggling with the following concepts:
SOLVED - How do to make a combobox populate with all the values from its ini file
SOLVED - How to identify if a selected value in any combobox is new (i.e. wasn't in the original list presented to the user)
SOLVED - (From above) How to write this new value to that comboboxes ini file (so it will appear in future lists for that combobox)
How to input two values from any given combobox
(From above) How to record two values for one field into the files ini file.
View 11 Replies
View Related
Aug 27, 2009
i have a table on sheet1
A B C
(Names) (Total) (Status)
1. John 500 Active
2. Dave 200 Active
3. Andrew 175 Active
4. John 225 Active
On my userform there are 2 comboboxes. In combobox 1 I have Rowsource as Column A (Names), if I select John I want Combobox 2 to list 500 & 225.
View 5 Replies
View Related
Mar 1, 2008
I'm sure this must have been answered before but I cant find it, sorry.....
I have 12 comboboxes on a worksheet. They are normally filled out by typing the first 3 characters of one of the entries in the list they are populated with.
I.e. the list starts;
AAC - Al Arish - Egypt - HEAR
AAN - Al Ain - United Arab Emirates - OMAN
AAW - Abottabad - Pakistan -
The first 3 character are unique to each line.
I'd like to be able to enter the 3 characters in the first combobox & then tab to the 2nd combobox, etc, etc. No problem in a userform but how on earth do I do that when the comboboxes are on the worksheet?
View 9 Replies
View Related
Jan 20, 2009
I have two comboboxes on a userform, they both get there list from the same formula. What I am trying to do is have the second combobox have it's selection preset based on the selection in combobox 1.
ie
Combobox1 = 6:00 AM
when you click on the dropdown for combobox2 i would like 6:00 AM to be the first selection possible, but I dont want it displayed in the box unless it is selected.
Dim timdat1(1 To 85)
For i = 1 To 85
timdat1(i) = Format(TimeSerial(5, (i + 1) * 15, 0) - Int(TimeSerial(5, (i + 1) * 15, 0)), "h:mm AM/PM")
Next i
combobox1.List = timdat1
combobox2.List = timdat1
i am at a loss for where to go from here
View 9 Replies
View Related
Jun 20, 2006
I am using the following code to determine whether a given range is the linked cell for a Combo Box by looping through the shapes collection.
Function LocateFormControl(OverRange As Range) As Shape
Dim objTemp As Shape
For Each objTemp In OverRange.Parent.Shapes
If Left(objTemp.name, 6) = "Drop D" Then
If WorksheetFunction.Substitute(objTemp.ControlFormat.linkedcell, "$", "") = WorksheetFunction.Substitute(OverRange.Address, "$", "") Then
Set LocateFormControl = objTemp
Exit Function
End If
End If
Next
Set LocateFormControl = Nothing
End Function
However, when I use this code on big worksheets with many other shape objects (such as Comments), the program runs very slowly. Is there a ComboBoxes Collection that I could use to avoid looping through all shapes on the worksheet, or is there a different, faster way to run this code?
View 2 Replies
View Related
Apr 12, 2010
Looking for examples of the following:
1. population a combobox in excel vba using code as opposed from a sheet
2. population a listboxin excel vba using code as opposed from a sheet
3. population a combobox in excel vba using text from a file as opposed from a sheet
4. Getting the values from a combobox on a form to populate cells on a worksheet
These list boxes and comboboxes will be on a form.
View 6 Replies
View Related
Apr 22, 2011
I totally understand how to make the combobox under form controls now but I am not having any success with the indirect function I was using as a list now that I have a combo box. I have attached the current form I am working on that just shows the list function still. How to convert this over to combo boxes with the indirect function?
I attached a second form with the feature I am asking about. It is just lacking the third list that I now have in place. (on the 1st attachment).
Attached Files
File Type: Corp MASTER (3).xlsx‎
File Type: Quote form (2).xlsx‎
View 8 Replies
View Related
Mar 3, 2014
Within a userform, I want to populate five combo boxes and with the values of 1 - 10. I looked through several excel websites and found several solutions. However, I could not figure out how to adopt their solutions to my specific case.
My naming convention for the combo boxes are: cmboPeriod1, cmboPeriod2, ... , cmboPeriod5.
[Code].........
I know my with statement is incorrect. I know the compiler will automatically think 'cmboPeriod' is a variable. I just don't know how to get it 'cmboPeriod & j' to do what I really intend for it to do.
View 4 Replies
View Related
May 7, 2009
I have a userform that has a text box. If user puts a number in it and click on proceed the userform must expand and display that many comboboxes. for e.g. if user inputs 8 and then click on proceed then there should be 8 comboboxes on the form. Is it possible to do?
View 4 Replies
View Related
Jun 10, 2009
How do I keep users from writing data into ComboBoxes on a UserForm?
View 4 Replies
View Related
Nov 27, 2009
I have 2 comboboxes created on a userform.
This is my code for both of them
View 13 Replies
View Related
