Excel Userform Combobox Looking Up Value From Worksheet Based On Other Values Picked
Jun 28, 2014
I have created a spreadsheet that is a roster to track mutliple crews and shifts.
I have a userform which is used to select a person on shift and assign them as being on either, Annual Leave, Personal Leave, Training.... on particular days.
I would like to have the shift combobox auto populate what shift the person is on based on the person and dates entered. All the information is there I just can't get the code to do this.
I was also thinking about blocks where the shift goes from DS to NS and if people are on leave over this time. I would need the combobox to know this also.
I have attached my workbook : New Annual Leave Spreadsheet.xlsm
View 9 Replies
ADVERTISEMENT
Jul 20, 2014
how can I build a userform to display all values from a row? in order to select a row, i was thinking to use a combobox to display values from one column and based on that selection, display all values in that row? the reason i want to use a userform is because i would also like to be able to edit that data.
View 3 Replies
View Related
Jan 15, 2014
Below is my current code. The strFind1 searches for a name within the database and then I need strFind2 to do a exact for a Subproject search and a partial search for everything containing the Subproject selected and other Subprojects. Currently, when the database entry in the worksheet includes Subproject 1 the search function works but when I have an entry that contains Subproject 1/Subproject 4 it does not find the entry. How can I expand the strFind2 to equal what is selected in the Combobox2 and find entries that have what is selected plus more text. I have set the line where I think everything is going wrong to a bold format.
[Code] .....
View 2 Replies
View Related
Feb 14, 2013
I am using the combo box that lists the loan officers number from the selection the loan officers name and branch is loaded. I want to be able also base on the officer selected add get the total new loans opened by that officer. I added an if statement that checks if the loan officers number from the "Oct_2012" is equal to the loan officers' selection from the combo box then add all the loans than match that criteria. I am not sure if what I am doing will work but when it reach to the For block it only reads the For statement and then go to the endif and don't execute the statement within the block.
Code:
Sub cmbLnOffNum_Change()
Dim idx As Long
Dim LnOffRow As Long
[Code]....
View 1 Replies
View Related
Jul 25, 2014
I'm trying to do some userform development with multiple dependent comboboxes, but I am having trouble populating the third and last combobox. First, I populate the 1st combobox on the userform initialization. I can get the first 2 comboboxes to populate correctly, but I can't seem to get the 3rd combobox to populate correctly.
The 3rd combobox takes the selection from the 2nd combobox and searches column "A" in the worksheet "Chassis Specs" and populates the 3rd combobox with data from column "B", there will be repeat items in column "A", but all those occurances should populate the 3rd combobox with the data from "B".
I am attaching my whole workbook.
Chassis Specs Info_Build_REV B.xlsm
View 14 Replies
View Related
Jan 17, 2007
I have a speradsheet for my work, which gives the user problems and solutions to our website. however I cant get the userform to recognise the named ranges I have added. (ie the first combobox wont recognise the rowsource, which is a named range).
View 9 Replies
View Related
Oct 1, 2009
I have one userform that loads combobox values upon userform Initialize. Though through a second userform changes can be made to anotherworkbook this workbook is saves any changes. when i close the second userform i need to rerun the 1st userform Initialize event to update the combobox's incase changes have been made.
View 5 Replies
View Related
Apr 9, 2012
I'm trying to make a userform that has 2 combo boxes. I have just 3 columns right now.
Procedure GrpADA-QSI DescProcedure GrpAnesthesia And
Drugs9210-LOCAL ANESTH/NO SURGAnesthesia And DrugsAnesthesia And
Drugs9212-TRIGEM BLOCK ANESTHCrowns And BridgeAnesthesia And
Drugs9215-LOCAL ANESTHESIADenturesAnesthesia And
[Code] ........
I copied and pasted Column A into Column C and then removed duplicates. I named Column C 'ValList' and placed it in the RowSource for ComboBox1. What I now want is for ComboBox2 to populate based on my selection in ComboBox1. There are no duplicates in Column B. Duplicates are in Column A.
I also named Column A 'Proc_Grp' and Column B 'ADA_QSI_Desc'
For the properties in Combobox2, I left the RowSource empty. (that's correct right?) Because there's going to be a code that links Combobox2 to Combobox1... I think...
View 6 Replies
View Related
May 30, 2008
I have a userForm to add holiday record into this worksheet. UserForm has one comboBox (userIDList) to search userID from ='Jan-July'!empName1 which is from A11 ( named Range as empName1). I am trying to add holiday details ( I mean the dates) in column T to CA ROW as per userID. I do not have any clue how could I pass the data (Holiday dates) from user form to the worksheet. Columns T10:ca10 titles are dated (01/01/2008,02/01/2008..... 29/02/2008). Do I have to use find or Select CASE.
At the moment I can locate empolyee as per userID and then select the row. I have attached the xl file.
Private Sub addButton_Click()
Dim sfind As String
Dim cl As Range
Dim fromDate, toDate As Date
'check for a value & show message
Sheet = "JanJuly"
Sheets(Sheet).Select
Application.ScreenUpdating = False
If Me.userIDList.Value = "" Then 'the textbox in userform
Me.userIDList.SetFocus
Exit Sub
Else..................................................
View 5 Replies
View Related
Feb 15, 2014
I am trying to have a ComboBox activate a specific sheet based on the value of "ComboBox1.Column(1)". I have many sheets to select from so i want to avoid using the Case method Here is a small sample of my code that isn't working:
[Code] .....
View 2 Replies
View Related
Jun 1, 2007
I am trying to create a userform to allow user to register their new team member. In the userform, I have textbox1 (new team member) & textbox2 ( name of their leader). Once both the textbox has been filled, the user need to click on the commandbutton, which will then add the newly registered team member to the combobox1 in the Sheet1 and then create a spreadsheet(tab with the Team member name) in a separate workbook, which corresponde with the name of their leader (as filled in textbox2 in the userform.
View 2 Replies
View Related
May 14, 2007
I'm running in to some problems on creating a Userform. I will try to explain it as best as possible. Right now I have a userform set up with 2 ComboBoxes. Here is my current code. It's not near complete.
Private Sub CommandButton1_Click()
Set ufStart = Worksheets("Data"). Range("AP4")
Set valNames = Worksheets("MasterData").Range("AA6")
Set valMonths = Worksheets("MasterData").Range("H3")
Set SelMonth = ComboBox2.ListIndex
Set SelName = ComboBox1.ListIndex
If TextBox1.Value > 0 Then
ufStart.Offset(SelName, SelMonth).Value = TextBox1
Else: End If
End Sub
The way it's supposed to go, is if TextBox1.Value is greater then 0... then go to UfStart and Offset by Row, which is ComboBox1 Selection Index Value and by Column, which is ComboBox2 Selection Index Value. I can't get the sub to get past Set SelMonth = ComboBox2.ListIndex.
View 6 Replies
View Related
Aug 15, 2008
I have created a User form with Combobox's, which in turn look up cell references in another sheet. In order to make maintainence of the combobox's as easy as possible I'd like the look up as many cells as possible, therefore anyone can easily add additional text to go into the user forms. However I don't want the blank spaces to show until something has been added.
View 9 Replies
View Related
Jul 26, 2012
I found this thread which deal with populating unique values in my listbox.
Here is the thread link. [URL] ........
What these line of code mean (the ones in red).
Code:
Private Sub UserForm_Initialize()
Dim v, e
With Sheets("maintenance").Range("c2:c500")
v = .Value
End With
With CreateObject("scripting.dictionary")
[Code] ......
Note : I simple tried to check the value of v by giving msgbox (v), just below the line v = .Value.
But I got run-time error 13 : Type mismatch error.
View 5 Replies
View Related
Sep 5, 2006
I have a userform with 15 Combo boxes & I would like to make sure all the enabled combo boxes (some may be disabled) have a value > 0 (Zero) before exiting the userform.
View 5 Replies
View Related
Feb 20, 2012
I have a series of cascading comboboxes on a sheet named report. In the first combo box,cboCategory, the user will have the option of selecting New, Existing, 401k, CPA Information. If the user selects CPA Information I need a user form named CPA to open.
View 4 Replies
View Related
Aug 2, 2013
I have this concept of a table of responses that have to be generated every month or on command. These reports export from a website into a certain format, its easy for me to adjust it so it can populate a the table. Although, i want to incorparate my date picker so everytime i pick a certain date a diffrent set of data will populate from the report. it seems very possible, just need to know where to go from. Either a VBA code or a fomula to allow for this to happen.
Heres and example..(cell linked to date picker)........column1 column2 column3,(date picker from microsoft),,each has a corresponding month
.....................................................................jan
.....................................................................feb
.....................................................................etc.
say i pick january, i want these columns to give me my data from a sheet or other workbook(the january report). i want everything linked so it will do this for virtually every month of the year. ive attached an example, the only thing i can think of is to make the cell that changes with when i pick a date to link to the report criteria.
View 1 Replies
View Related
Mar 17, 2014
I have created a userform and it works fine.Following code is assigned to commandbutton to transfer data to sheet "FT".
[Code].....
I want to add that : If value in combobox2 is “ KT” then all entries are copied to sheet3
Otherwise copy everything in sheet”FT”
Other parameters remain same.
View 4 Replies
View Related
Dec 21, 2009
I have a userform that has a "payment type" combo box
This box consists of:
View 6 Replies
View Related
Aug 15, 2014
I have this long list of data (company name, company ID and OIC) on sheet 1.
Then on sheet 2, user will key in the company name manually then the company ID and OIC name will auto-populate (i did the match index already on this one) before filling in the other cells.
How do I make the cell to auto-complete the company name by using the list of data in the other sheet? Do I use the combo box? If I use the combo box, will it affect the match index formula i created for company ID and OIC?
View 1 Replies
View Related
Jun 18, 2014
I am looking to modify my combobox from a userform to Filter the list based on the first entry on what the user will add.
I have a code that will search for all entries in my "Control" sheet and passed this on to my combobox:
[Code] ......
I have, within a module, the following function code that the user find the entry by adding the first 3 letters of the search they need:
[Code] .....
I would like to know what to change in the function to filter the list, once the user adds a first letter and then press the dropdown from the combobox, to filter the list based on all entries from the first letter to only show entries with that letter.
If A is entered, then, show only the entries strating with the starting letter A, but at the same time if the user adds more letter to filter to the first then second letter:
If AB is inserted, then, show entries with AB only.
Where do I add this code also, is this another function or this could be inserted within my function?
View 7 Replies
View Related
Aug 21, 2014
Excel 2007 and very new to VBA...
I have a userform (named "QAReviewForm") with a combobox (named "cboSupName"). I want it to populate with my named range "SupList".
FYI, the SupList is found on sheet 2, "Administrative Menu" in column E. E1 contains the heading "Supervisors", and my named range formula is
Formula:
[Code ] .....
When it runs, my form opens and the combobox is there, but nothing appears as options for me to select (yes, I have some values in column E).
View 11 Replies
View Related
May 8, 2012
I have a userform that has a combobox in it that also has an OK button and a Cancel Button. Need getting the combobox to pull in the correct list? The data for this list will be in cells A2 through A16. But not all 15 lines will always have data in them so I'd like to only show the ones with data. Then next issue i have is I would like the OK button on this userform to actually erase the data the user has clikced on in the combobox. It's a list of kids that the userform is asking which one needs to be deleted. So it might be the kid in cell A9. so when the user clicks on that kid in the combobox form a9 and hits ok I'd like to have all the kids below A9 move up one row.
Windows XP
excel 2003
View 6 Replies
View Related
Jun 10, 2013
I have a user form that has one combo box on it that right now references one column of data.
Now I am being asked to have three columns of data and the combo box to show one of the three when a certain criteria is met.
I believe it would be easiest to have an additional combox with a change event when the box is populated with "whatever" in the field
So S:3 to S:5 have A, B, C
And EC:1-EC:59, ED:1-ED:59, EE:1-EE:59 contain the data that should show when S:3, S:4, or S:5 is selected.
If S:3 is selected then the list in EC:1 - EC59 would show and so on.
View 2 Replies
View Related
Sep 1, 2006
i have a user form with 4 combo boxes and one text box. what i've tried to do is make it so that if the month combo box reads january, then all of the info is placed in a worksheet called january. at the moment it is putting all the info inputted into one sheet. if some one could take a peek at the code below.
If cbomonth.Value = January Then
Sheets("January").Select
Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) Or IsEmpty(ActiveCell.Offset(0, 1)) Or IsEmpty.....................
View 9 Replies
View Related
Mar 5, 2008
I'm currently building an Excel database. This database has a few comboboxes and checkboxes that are tied to each other. If I select the combobox option of "Years", the "Quarters" combobox must be greyed out. In addition depending on which option is chosen, the relevant check boxes must be enabeled or disabled. I've written some code which runs fine, until I try and activate another macro button or if I add and rename a sheet(which is not linked to anything). What should I do? Here is a sample of my code. I have a feeling it's really obviouse but I cant see it..
Private Sub ComboBox2_Change()
If Sheets("InputSheet").ComboBox2 = "Years" Then
Sheets("InputSheet").ComboBox3.Value = ""
Sheets("InputSheet").ComboBox3.Enabled = False
Sheets("InputSheet").CheckBox6.Enabled = True
Sheets("InputSheet").CheckBox7.Enabled = True
Sheets("InputSheet").CheckBox8.Enabled = True
Sheets("InputSheet").CheckBox9.Enabled = True
Sheets("InputSheet").CheckBox10.Enabled = True
Sheets("InputSheet").CheckBox11.Enabled = True
Sheets("InputSheet").CheckBox12.Enabled = True
Sheets("InputSheet").CheckBox13.Enabled = True
Sheets("InputSheet").CheckBox14.Enabled = True
End If...................
View 3 Replies
View Related
May 16, 2008
I am working on a spreadsheet for a client. I am using Excel 2007 and she is using Excel 2000. I have created a emplate for her to enter survey data into. Using the following code, I have created combo boxes for the drop-down menus so the client can tab from one cell to the next while entering data:
Option Explicit
Private Sub TempCombo_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
'Hide combo box and move to next cell on Enter and Tab
Select Case KeyCode
Case 9
ActiveCell.Offset(0, 1).Activate
Case 13
ActiveCell.Offset(1, 0).Activate
Case Else
'do nothing
End Select
End Sub.....................
View 6 Replies
View Related
Jan 4, 2007
I have a worksheet in which i have a set of data similar to the following (i have used colons as delimiters in this example).........
Basically what i want is 2 combo boxes. The first combo bow will allow you to select the phone type (e.g. Motorola PEBL, Motorola RAZR etc etc). The second combo box will have a list of items which match up to the value selected in the first combo box. Once the item has been selected from the 2nd combo box then the relevant matching data will be shown in a group of cells on the worksheet on which the combo boxes reside.
I know that this could be done by using the data validation feature if my data were laid out in a different way however it is not possible for me to re-lay out all the data in my worksheet. I have been told that there my problem copuld be potentially solved by using Excel VB but unfortanately my VB skills are quite weak.
View 2 Replies
View Related
Mar 6, 2014
I have a main worksheet that has two combo boxes. The first combo box has a list of machines. The second combo box brings up a list of models for the type of machine selected. I would like to insert a command button, that when pressed will open a different worksheet with the specs of the model selected in the second combo box. I am having trouble finding a starting point for the code to have the command button perform this.
View 6 Replies
View Related
Nov 16, 2011
I have a vba to hide certain rows based on 4 values (either 15, 20, 25, 30) are the choices in the 'ComboBox' drop down list. I also have the rows grouped so i need to make sure to collapse those back each time. Here is the vba i'm using, but its not quite right for some reason.
PHP Code:
Private Sub ComboBox1_Change()
If Range("J8").Value = "15" Then
Range("A185:A244").EntireRow.Hidden = True
Range("A245:A304").EntireRow.Hidden = True
Range("A305:A364").EntireRow.Hidden = True
Range("A365").EntireRow.Hidden = False
[Code]...
View 1 Replies
View Related
