I would like to populate a combobox with text from two different ranges:
I have an ID No in column A (range = ID) and a descripton in Column B (range = Description)
I would like the following to appear in the combobox:
"J.034 Kitchen Bench" at the moment I can only get J.034 from the following Private Sub UserForm_Initialize() Dim cBase As Range Dim ws As Worksheet Set ws = Worksheets("Matdata") For Each cBase In ws.Range("ID") With Me.cboBase .AddItem cBase.Value .List(.ListCount - 1, 1) = cBase.Offset(0, 1).Value End With Next cBase Me.cboBase.SetFocus HideTitleBar Me End Sub
I have successfully used the course booking form as a base for an input form in an accounting workbook that has several pages. (Thanks to Roy and others for the info; the course booking form on [url]is the best explained piece I have found on the net)
What I would like to do is use a range of cells to populate the combobox so that a user can make a choice from the account descriptions (from a sheet named Intro, b63:99) and have the form return the account number (Intro, a63:a99) to the relevant cell elsewhere in the workbook. The user would then have meaningful descriptions rather than numbers to choose from.
I am having trouble with populating a combo box on a user form. I wish for the combo box to be populated using the names of the sheets of the workbook. I am using sheet1 as the control sheet where a user form will open up from this sheet, this will allow me to select the sheet that I require from a combo box that is placed on the form however I do not seam to be able to code the VBA to populate the combo box with the sheet names.
Here is what i am trying to achieve. If the date 2/20/2010 is located at F53 & the cell next to it at H53 is populated with a number between 1 & 16, then i want the cell at J11 (42 rows further up) to auto populate with the number 1. When this occurs the cells beneath this, from J12 to J52 should also auto populate with the with consecutive numbers from 2 to 42. Would also like to see the cells with numbers 1 to 28, automatically format to orange & the cells containing numbers 29 to 42 automatically format to yellow. I plan to have this condition repeat several times later in the year, at dates that are to be decided. When these dates are decided i want to be able to enter a number from 1 to 16 & next to the date & all of the above automatically occurs.
I am trying to populate a combo box with two columns of data read from two consecutive (or non consecutive) columns of data. The following code is what I have come up with after visiting several of the previous threads on similar topics (couldn't find one that gave a general, concise solution).
Private Sub UserForm_Initialize() Dim lngRow As Long Dim lngRowtot As Integer Parameters1.SG1.Clear 'Clear combobox SG1 in userform named Parameters1 'Find the number of rows required for the array and combo box lngRow = 12 'Start searching on row 12 Do While Sheets("Timber Properties").Range("H" & lngRow).Value <> "" 'Read until empty col H lngRowtot = lngRow - 11 lngRow = lngRow + 1 Loop Dim MyArray(1 To lngRowtot, 1 To lngRowtot) As String 'I get an error with the last ingRowtot on this line.....................
I have a userform (uf_PaxInput) with a Combobox (cmb_flight_dest) which I am trying to populate from a worksheet range ("Dest") using the following code:
VB: Sub LoadForm() With uf_PaxInput.cmb_flight_dest For Each Item In Range("Dest") .AddItem Next Item End With uf_PaxInput.Show End Sub
The code seems to run okay however whent the form loads the combobox drops down to show only blank items to select. These blank items number them same number of items that are in my named range ("Dest").
The '-1' allows for a heading at the top of the column. This works fine when the list in col. L has more than one entry under the heading. But with only one entry I get 'Runtime error 381 - Could not set the List property, Invalid property array index' and the debug highlights on
I've done define name to read column C until G in 1st row, N define another name from column C until G in 2nd row.For example; NameRanges for "Year" =OFFSET(Sheet2!$C$1,0,0,1,COUNTA(Sheet2!$1:$1)-2), for "Thickness" = OFFSET(Sheet2!$C$2,0,0,1,COUNTA(Sheet2!$2:$2)-3)
I want to display list of year in combobox and then, when the user click on the first item in combobox, the textbox will display the value that correspond to the combobox. For example; when the user click Year = 1990 in the combobox, the textbox will take value from excel and display thickness for year 1990 which is 4.
This is my code, the code in UserForm_Initialize is working. unfortunately, coding to display value in TextBox1, doesnt work.
I would like to know how to automatically populate a combobox with the data in a certain range. Attached is a simple workbook with the example. I know how to do this with the additem. But I dont know how to create the array or userform initialize to populate the combobox with the range.
I need to populate a combo box on a form with the column name (A,B,C...etc) and the first row data. I need the code to check all columns in-case of missing column data.
Its important to note the data will be dynamic. In my add in, a form opens on requests and asks the user which column he needs to action data on. this could be on any one of several non similar spreadsheets.
E.G.: Combox to hold the following data (see column D has no data or header row):
Column A - Date Column B - Rep Column C - Customer Column D - Column E - Product
I'm trying to get some vba to automatically find the range of data in a spreadsheet (So start from a specified point and loop until it's blank) and populate a combobox with this range accordingly. I have read numerous articles and posts but nothing seems to work!!
The code below extracts unique distinct values from column A, except cell A1. Then the code adds the extracted unique distinct values to the combo box.
Sub FilterUniqueData() Dim Lrow As Long, test As New Collection Dim Value As Variant, temp() As Variant ReDim temp(0) On Error Resume Next With Worksheets("Sheet1")
The vba code below copies the selected value to cell C5 whenever the combobox is selected
Copy the code into a standard module.Assign this macro to the combobox
Sub SelectedValue() With Worksheets("Sheet1").Shapes("Drop Down 1").ControlFormat Worksheets("Sheet1").Range("C5") = .List(.Value) End With End Sub
Vba code belows instanly refresh the combobox when values are added/edited or removed from column A,
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("$A:$A")) Is Nothing Then Call FilterUniqueData End If End Sub
My problem is this:
Everything will be working fine if the all combobox and data can be found in 1 sheet, but how could i make some tweaks if the combo box is located in sheet 2 provided your data to be populated is in sheet 1, i tried everything but the combobox doesnt update,
I have data that is on a separate .txt file (the source file) that resembles this;
A B C D E F
1 Case District Pct Division Level1 Level2
2 305035 0 20 72
Using VBA from within the destination workbook, I would like to use an input box that allows the user to request/specify what numerical number (0 through 55) in Column B of the txt file (the source file) will be used to determine what data to copy to the destination file. The cells to copy to the destination file would be from Column A through Column G of the source file onto Sheet1, E2 of the destination file.
Column B of the Source file will only contain numbers. However there will be numerous identical numbers in Column B. All of which will need to be copied onto the destination workbook.
From the sample above, if the user were to enter '15' in the input box, the desired result on the destination sheet beginning at E2 would look like this;
i'm having a hard time with trying to populate data from a userform to a specific column under a specific sheet. I've attached the file if you want to look at it. The file is basically a exam type with user logging and auto score computing.
The file is made of 6 sheets, 1st sheet is the log-in page where takers are required to enter their ID number and last name. 3 sheets of dummy test items, an Admin sheet that computes the score per test item and Database which I'm trying to compile all data in one column.
What I'm trying to do is that when they enter their ID and Last Name and then answer the 3 tests, they will be recorded in the database sheet in 1 column containg their ID, name and all the test results so that checking,grading and identifying who took the test would be easy.
So far I think I've done the part for the scores but I can't seem to get the userform for emp# and last name to populate on the field marked in the database.
I've been writing a UserForm in Excel that serves the purpose of a front-end contact/reporting spreadsheet.
I've managed to get a multitude of elements to work together as of yet, however have been having problems writing data to the correct place on the spreadsheet.
What I've already achieved:
The first interactive portion of the userform currently has a TextBox (ArtistAgentAddEmail) - this is the email that we would like inputting on the spreadsheet.
a ComboBox which is populated on 'Userform_Initialize' into an array (ArtistAgentAddDestination) - The range of this array is A1:AC1.
What I would like to do next:
The issue that I've been having is getting the email to be written into the correct column. Once I have typed in my email and chosen my destination from the combobox, I would like to write this email address in the column of the destination chosen.
E.G. The first 5 elements of my ComboBox array are: Neil O'Brien, CAA, CODA, ITB and PRIMARY. If i got an email from somebody in the CAA organisation to add a new contact to the reporting, I would like to be able to add this address via the userform underneath the previously added contact.
The code that I am currently using for my confirm button is:
I need to populated a specific range of cells based on a value of any kind in a header cell. Attached is a sample of the end results. Please remember each header cells needs to determine what happens the range of cells below the header cells.
3 work sheets nameWorksheets "InfoData" to save month NameWorksheet "Jan" for JanuaryWorksheet "Feb" for February1 x ComboBox = monthBox 4 x CommandButton = Week1Button,Week2Button,Week3Button,Week4Button --------------- I have Problem opening worksheet from userform. Name of the months are in Combox. So when I select month January from comboBox and click on Week 1 command Button it should open worksheet named "Jan" and Select Cells(2,1) to show Week 1. And Same for week 2, week 3 and week4 but will select different Range or cells. I have attached the screenshot of userform and also the workbook.
Private Sub Week1Button_Click()
For i = 1 To 13
If monthBox.Value = Worksheets("infoData").Cells(i, 1) Then
I've got a big file with a large dataset. One of the sheets works as a screening for the data, so everytime I populate an ID number in say cell C3, the rest of the sheet updates as per the formulas I have in place. However, I need to write a code that takes ID numbers one by one from a sheet, populates them into my screening sheet, updates the sheet and saves it.
I'm need to add a droplist with periods 1,2,3,4 etc... that references a cell then populates that same reference in a cell raange. Example: cell E1 contains the droplist with periods 1-4 as choices to select from, when you select period 1 it populates the dates jan 15 in cells E4:E7, if you select period 2 it populates dates jan 31, etc. how to add the droplist, how to expand the list, show the formula and how to expand it as I will have more data than this short example.