I have 10 comboboxes - all require exactly the same list. Rather than having to copy the list 10 times in the coding - and changing the combobox name from listcode1, listcode2 etc, is there a loop code which I can add to do this for me??
Private Sub Userform_Initialize()
'Empty txtdate
txtdate.Value = ""[code].....
I am not sure if this can be done easily but essentially what I have is a userform that fills out a form that will used by several different people and saved to a central location.
There is a blank combobox currently labeled "Supplier:" that each person fills out each time on the userform (there are no dropdowns yet).
What I am wanting to do is for every person who saves (I have coded the save button) with the "Supplier:" combobox filled out to save what they entered and input that into the combobox dropdown so when the next person uses the userform the entry the previous person entered would be in the combobox as an option to select.
Essentially, a combobox populates itself based on what previous users have entered into it.
I have a Userform with 2 comboboxes.
combobox1 is filled with values. I want the user to select one from combobox1 and combobox2 get filled in with unique values based on combobox1
Sheets Quote has 2 columns
Customer Job
Stanley 1
Stanley 2
Fred 1
Stanley 3
Stanley 3
Stanley 3
Stanley 4
Fred 1
combobox1 is filled in with Stanley and Fred
based on the user selection (as an example Stanley)
I want combobox2 values to be 1,2,3,4 (having 3 listed only once)
Is there a way to modify the code below to verify that the (e) .offset(-1,0).value = combobox1
Is there a different option to use?
Code:
Public Sub fill_job_num()
Dim v, e
On Error GoTo error1
With Sheets("Quotes").Range("B2:B50000")
v = .Value
End With
With CreateObject("scripting.dictionary")
[code].....
I have a userform1 on there a combobox1 and a frame1. In that frame1 I want a number of labels to be added named "name" & number. The number varies depending on the value in combobox1. If combobox1 = "1" then I want 20 labels to be added. if combobox1="2" then I want 60 labels to be displayed.
label height=12, width=102, top=6 and left=6
for each following label the top = top + 14
and after each 10th label left = left + 120
If there's more than 30 labels then I need a scrollbar on the bottom of the frame to be displayed and so be able to see the other labels.
If the first value in combobox1 is "1" is selected and all is displayed and then I select "2" that is deletes all the current labels in the frame and then add 60 new labels.
I have 2 combobox in userform. Both the comboboxes have the same list down value and I have applied as follows
VB:
combobox1.value = "Transport"
combobox1.value = "Paper"
combobox1.value = "Pencil"
Like this I have long list
In the combobox2 same list has been applied
Is there a way in which by one command the list is applicable to both the comboboxes, that would save me on the length of VBA.
I have five comboboxes in userform1, they are link to Column A, Column B, COlumn E, Column G and Column Z of "Reference" worksheets respectively. The values in each column could contain duplicate values. However, I'd like to only display the unique values in those combo boxes and I have no clue how to achieve this.
View 7 Replies View RelatedI have just discovered the exciting world of the additional controls that can be added to userforms. provide me with some sample code to make a progress bar increment and to activate a windows media avi file from a user form. I only need to know the code (I know how to add the controls etc)
View 9 Replies View RelatedThe ultimate goal is to create a Userform at run-time.
The problem is writing event code for that Userform.
The current situation is:
I have a userform. (Created with the VBE, not at run-time.) There is one command button on that userform. The code module for that userform is this
Option Explicit ...
i want to use a listbox or combobox on a userform with the values coming from column A in the MAIN sheet. what i need is if the colour i want is not there i type the new colour in it then adds the value to the end of values in coloumn A and too the list for the next time i use the userform. is it possible to do this and how?
View 3 Replies View RelatedI have seen some code around that allows scrolling in listboxes using the mousewheel. I have also seen some code here on the forums about scrolling through a combobox embedded in a workbook. I have tried adapting both of these to suit a combobox on a userform but I cannot get either to work.
Here is the code that I used for the embedded combobox; I added it to the base code for the userform
I then added the following code to a standard module as stated in the forum post;
In the first section of the code there is a msgbox that never gets shown so I don't think code is recognizing the mouse wheel at all.
The second set of code that I used was set up to work with a listbox and it works perfectly with a listbox control. The problem is I cannot adapt it to suit a combobox and if I use a breakpoint to see what is happening Excel freezes.
The following code is added to the userforms code;
Then in a standard module I added the following code;
I added this section of the code;
I think this should only work on a Combobox but a Combobox does not appear to support the TopIndex value. Is there a way this can be setup to work with a userform combobox?
Sorry about the huge amount of code.
I have 4 columns, If column B (Ref No) is filled in, the adjacent columns, C,D and E become mandatory and an input box pops up on screen one after the other for entry into each column.
My problem is that I require a drop down list and not a input box for the final column (status). So i have created a userform with a combobox dropdown. I am having problems connecting the combobox selection to the final column, and for the selected combobox item to go into the right cell like the input box entry currently does.
Attachment 53209test743.xls
I have attached a sheet and also you can see the code below.
Code in Sheet
VB:
Public SaveVal1
Public SaveVal2
Public SaveVal3
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
[Code] .....
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).
I have a list of employees that can be assigned to tasks, the list is large enough that I cannot remember who is assigned to what.
All the employees assigned major tasks are automatically filtered out but these are employees with administrative tasks that should only be assigned as a last resort. I would like to assign the employees with administrative tasks a gray, italic, strike-out or some other font to indicate that they are different.
I assume this is done during the Add Item process...
I've prepared an excel file with a pivot table. Now I would like to change the Pivot "Report filter" by using Textbox on Userform. I've attached an excel file as an example.
View 2 Replies View RelatedI have a userform, on the user form I have a combo box. when i select an item from the combobox list. I want it to show only that item in the pivot table.
Code:
Dim i As Integer
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Description")
For i = 1 To .PivotItems.Count
If i = ComboBox2 Then
.PivotItems(i).Visible = True
Else
.PivotItems(i).Visible = False
End If
Next
End With
Im trying to make some code to go through a list of numbers, and pick the next highest number from the one entered in a form. Then I need to do some processing with that number (i need to create a worksheet with that number as the name, and place that number in a few cells on that worksheet, and the main worksheet, but thats all stuff i think i can do).
View 14 Replies View RelatedI have a userform that I'm using to add data to a worksheet, with the following
Private Sub CommandButton1_Click()
Dim OutSH As Worksheet
Set OutSH = Sheets("Sheet1")
OutSH.Cells(nextrow2, 1).Value = Surname.Value
OutSH.cells(nextrow2,2).value = ID.value
OutSH.cells(nextrow2,3).value = Date.value
......
I need to ensure that duplicate entries are not made for the same person on the same date. The ID is unique to each person.
IF statement that can check for a duplicate and then come up with a dialouge box with some custom text, and then exiting the sub?
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 RelatedI 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 RelatedI want to add "Data Validation List" by VBA code.
If in column "A" I have 100 rows of data, I would like that in column be will be 100 "combos" with reference to certaing range.
I have a userform with a combobox that has three items to choose from: Blue, Red, and Yellow. When a user selects one of those options, I would like another combobox to appear on my userform with a specific list for that option.
View 2 Replies View RelatedI'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...
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 two sheets in my workbook:
J_Database, a prototypical database
J_ComData, a custom worksheet that prints out an invoice-like document.
I have a combobox (cboName) in a userform that is populated by a dynamic range from J_Database. Once I hit my command button, I would like the program to loop through all of the records, one-by-one, and input the values from the combobox, and drop them in cell A1 of J_ComData.
From here, the remaining information about the combobox value from J_Database will be retrieved and J_ComData will be printed.
I was working with the following code:
Code:
Private Sub cmdPrint_Click()
If Trim(Me.cboName.Value) "" Then
'Unhide the printable paradigm sheet
Sheets("J_Pref").Select
Sheets("J_Para").Visible = True
'Drop judge paradigm userform information in the sheet
Sheets("J_ComData").Select
[code].....
This code works if I select just one name, have it populate A1 with that name, and then print out the document. However, writing the code so it will just print through all of them at one time.
In my excel sheet, from B2 to B20 i have datas like,
aaa
bbb
ccc
aaa
ccc
ddd
rrr
ttt
bbb
rrr
ttt
I want to add this to my combobox1 with out repeation as, 'aaa' should be listed in combobox once. What is the macro for this or we can do it in Combobox properties itself?
method of looping through objects to make changes.
I have 12 comboboxes on a userform which I have set to dropdown list and want to populate during the userform initialization. Each combobox is called "pt*" where * is a number from 1 - 12. I have currently set up my code with 12 lines, one for each combobox to apply the same dropdown list to each of the 12 boxes.
ie
Code:
pt1.rowsource = ptsource
(where ptsource contains a range reference on the worksheet)
I'm interested to learn how to improve my code to loop through 1-12 and apply the rowsource to each combobox.
I did search through some past questions but could only come up with a method which seemed to step through every combobox on a worksheet rather than userform and in my case, I have two other comboboxes on the userform which I do not want to add this rowsource to, only the "pt*" named comboboxes.
I'm having difficulty assigning multiple macros to an active x combo box. I want a different macro to run every time the user selects a different location from the combo box. I can assign one macro to the button no problem, but don't know how to incorporate more. The two separate macros I want to run are:
Sub Atlanta()
'
' Atlanta Macro
[Code]....
I've got a database and want to put the names that are in one culumn of the Worksheet into a combobox.
I use a Loop and the addItem procedure.
The thing is that there are many names that are the same, just diferent records for the same company...
How do i do that every name only appears once in the ComboBox.
I need to add items to a combobox without adding duplicates or empty data. I am able to add without empty data but how to ignore the duplicates.
Here is what I have...
Code:
Dim GL As Range
Dim ws1 As Worksheet
Set ws1 = Worksheets("Main")
For Each GL In ws1.Range("MainGL")
If IsEmpty(GL) Then
'do nothing
Else
ComboBox2.AddItem GL.Value
End If
Next GL
How do I add the column heads (e.g. A1, B1, C1....) as items to a combobox or listbox if they are not empty?
View 9 Replies View Related