Looping UserForm ComboBox

Hi

I would like to use the code below in a loop so that i can populate numerous 
ComboBoxes within my UserForm with the same list.

I am not sure how to go about this, and would appreciate any help.

Thanks

With ComboBox7
    Dim MonthArray As Variant
    MonthArray = Split("01|02|03|04|05|06|07|08|09|10|11|12|", "|")
    ComboBox7.List = MonthArray
End With
0
Utf
6/6/2010 8:36:37 AM
word.docmanagement 5542 articles. 2 followers. Follow

4 Replies
2178 Views

Similar Articles

[PageSpeed] 54

Something like:

Option Explicit
Private MonthArray As Variant
Private Sub UserForm_Initialize()
    MonthArray = Split("01|02|03|04|05|06|07|08|09|10|11|12|", "|")
    Me.ComboBox1.list = MonthArray
    Me.ComboBox2.list = MonthArray
    Me.ComboBox3.list = MonthArray
    Me.ComboBox4.list = MonthArray
    Me.ComboBox5.list = MonthArray
End Sub

will do the job

-- 
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor -  Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


"Owen" <Owen@discussions.microsoft.com> wrote in message 
news:B4F28044-8A5D-464C-A43A-2696531ECF2C@microsoft.com...
> Hi
>
> I would like to use the code below in a loop so that i can populate 
> numerous
> ComboBoxes within my UserForm with the same list.
>
> I am not sure how to go about this, and would appreciate any help.
>
> Thanks
>
> With ComboBox7
>    Dim MonthArray As Variant
>    MonthArray = Split("01|02|03|04|05|06|07|08|09|10|11|12|", "|")
>    ComboBox7.List = MonthArray
> End With 


0
Graham
6/6/2010 9:22:23 AM
Thanks Graham...it did the trick.

"Graham Mayor" wrote:

> Something like:
> 
> Option Explicit
> Private MonthArray As Variant
> Private Sub UserForm_Initialize()
>     MonthArray = Split("01|02|03|04|05|06|07|08|09|10|11|12|", "|")
>     Me.ComboBox1.list = MonthArray
>     Me.ComboBox2.list = MonthArray
>     Me.ComboBox3.list = MonthArray
>     Me.ComboBox4.list = MonthArray
>     Me.ComboBox5.list = MonthArray
> End Sub
> 
> will do the job
> 
> -- 
> <>>< ><<> ><<> <>>< ><<> <>>< <>><<>
> Graham Mayor -  Word MVP
> 
> My web site www.gmayor.com
> Word MVP web site http://word.mvps.org
> <>>< ><<> ><<> <>>< ><<> <>>< <>><<>
> 
> 
> "Owen" <Owen@discussions.microsoft.com> wrote in message 
> news:B4F28044-8A5D-464C-A43A-2696531ECF2C@microsoft.com...
> > Hi
> >
> > I would like to use the code below in a loop so that i can populate 
> > numerous
> > ComboBoxes within my UserForm with the same list.
> >
> > I am not sure how to go about this, and would appreciate any help.
> >
> > Thanks
> >
> > With ComboBox7
> >    Dim MonthArray As Variant
> >    MonthArray = Split("01|02|03|04|05|06|07|08|09|10|11|12|", "|")
> >    ComboBox7.List = MonthArray
> > End With 
> 
> 
> .
> 
0
Utf
6/6/2010 11:59:19 AM
You are welcome :)

-- 
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor -  Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


"Owen" <Owen@discussions.microsoft.com> wrote in message 
news:4629F442-49B7-4252-BF2F-B30A7F06C681@microsoft.com...
> Thanks Graham...it did the trick.
>
> "Graham Mayor" wrote:
>
>> Something like:
>>
>> Option Explicit
>> Private MonthArray As Variant
>> Private Sub UserForm_Initialize()
>>     MonthArray = Split("01|02|03|04|05|06|07|08|09|10|11|12|", "|")
>>     Me.ComboBox1.list = MonthArray
>>     Me.ComboBox2.list = MonthArray
>>     Me.ComboBox3.list = MonthArray
>>     Me.ComboBox4.list = MonthArray
>>     Me.ComboBox5.list = MonthArray
>> End Sub
>>
>> will do the job
>>
>> -- 
>> <>>< ><<> ><<> <>>< ><<> <>>< <>><<>
>> Graham Mayor -  Word MVP
>>
>> My web site www.gmayor.com
>> Word MVP web site http://word.mvps.org
>> <>>< ><<> ><<> <>>< ><<> <>>< <>><<>
>>
>>
>> "Owen" <Owen@discussions.microsoft.com> wrote in message
>> news:B4F28044-8A5D-464C-A43A-2696531ECF2C@microsoft.com...
>> > Hi
>> >
>> > I would like to use the code below in a loop so that i can populate
>> > numerous
>> > ComboBoxes within my UserForm with the same list.
>> >
>> > I am not sure how to go about this, and would appreciate any help.
>> >
>> > Thanks
>> >
>> > With ComboBox7
>> >    Dim MonthArray As Variant
>> >    MonthArray = Split("01|02|03|04|05|06|07|08|09|10|11|12|", "|")
>> >    ComboBox7.List = MonthArray
>> > End With
>>
>>
>> .
>> 


0
Graham
6/6/2010 12:25:16 PM
Graham's answer is will do the job quite well in this case.  There are a few
other techniques that you might put in your toolbox for cases where the
number of controls involved is large:

The first loops through like named controls with an index number:

Private MonthArray As Variant
Private Sub UserForm_Initialize()
Dim i As Long
Dim CtrNameArray As String
CtrNameArray = Split("ComboBox1|ComboBox1|ComboBox1|ComboBox1|ComboBox1")
MonthArray = Split("01|02|03|04|05|06|07|08|09|10|11|12|", "|")
For i = 1 To 5 'or to whatever number you need.
  Me.Controls("ComboBox" & i).List = MonthArray
Next i
End Sub

The other uses a second array to provide uniquely named controls to the loop:

Private Sub UserForm_Initialize()
Dim i As Long
Dim CtrNameArray() As String 'Variant
CtrNameArray = Split
("ComboBoxAlpha|ComboBoxBravo|ComboBoxCharlie|ComboBoxDelta|ComboBoxEcho",
"|")
MonthArray = Split("01|02|03|04|05|06|07|08|09|10|11|12|", "|")
For i = 0 To UBound(CtrNameArray)
  Me.Controls(CtrNameArray(i)).List = MonthArray
Next i
End Sub



Owen wrote:
>Hi
>
>I would like to use the code below in a loop so that i can populate numerous 
>ComboBoxes within my UserForm with the same list.
>
>I am not sure how to go about this, and would appreciate any help.
>
>Thanks
>
>With ComboBox7
>    Dim MonthArray As Variant
>    MonthArray = Split("01|02|03|04|05|06|07|08|09|10|11|12|", "|")
>    ComboBox7.List = MonthArray
>End With

-- 
Greg Maxey

Please visit my web site http://gregmaxey.mvps.org

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/word-docmanagement/201006/1

0
Greg_Maxey
6/6/2010 1:33:56 PM
Reply:

Similar Artilces:

how to resize combobox control during runtime
Hi how can i resize the width of the combobox control during run time? I tried using the MoveWindow command it helps but it redraws all the items inside the dialog box so the shortcut key for label text. Or text that were underlined before were not underlined anymore. Pls help. Thank you. On Feb 15, 5:07 am, "ca" <cecille.regi...@gmail.com> wrote: > Hi how can i resize the width of the combobox control during run time? > > I tried using the MoveWindow command it helps but it redraws all the items > inside the dialog box so the shortcut key for label text....

My "Do While...Loop" wont loop and delete the spaces between my digits.
I am trying to eliminate the spaces between digits in Australian Business Numbers which have the format "NN NNN NNN NNN" to produce an eleven digit number with no spaces. I have written a function to do this that appears to work. I now want to call this function from a subroutine to change about 3000 numbers. The subroutine is causing me headaches. Here is my space-eliminating function: Function RemoveSpaces(CellContents As String) As String Do While InStr(1, CellContents, " ") > 0 CellContents = Replace(CellContents, " ", "") Loop Re...

Repost: Loop through files
I posted this earlier, but it never made it to the screen so I'm reposting - hope it doesn't duplicate the earlier one. I need to loop through all currently open workbooks and display a message box that tells me the file name and whether or not the workbook is visible. Your example code is what I need. Thanks in advance for your assistance. Hi, Sub ShowThem() Dim oBook As Workbook For Each oBook In Workbooks MsgBox "Book " & vbNewLine & oBook.FullName _ & vbNewLine & "is " & _ IIf(oBook.Windows(1).Visible...

Loop?
I really am having a bad week... I have a workbook with two sheets; 'main' & 'not complete'. On the main sheet I have 10 columns of data, row 2 to 20 (More rows can be added so may be more). What I need to do is: for each row, if column I = 1 and J = "" then I need to copy some of the data from that row (column A,B,F & G) to the sheet called 'not complete'. the cell in column J will then be changed to 1. Repeat for each row making sure the sheet called 'not complete' does not have any blank rows. Hope that makes sense a...

Loop/Repeat Code
A2007 I need to run code while the record is displayed on a form, and must run this for all records. I have on the Click event of a button on the form: DoCmd.GoToRecord , "", acFirst Call MyFunction It is MyFunction I need help with: DoCmd 'Run some code and queries' DoCmd.GoToRecord , "", acNext Need help here to repeat/loop until the last record. Thanks in advance. ...

Mail Loops
Is there anyway on exch 5.5 to limit the number of times a mail is resent ie when the user sets up a forwarding rule to a home account and screws up the address a mail loop is formed. I would like to set the number of retries to say 6. Help please tia No. Rules are set to fire when the e-mail is received. Only OOF is set to fire only once per recipient. As far as the rule is concerned, each message is separate and must be forwarded on to the home account. Do yourself a favor and disable forwarding to the internet. Give them another option to get e-mails while they are home. If you need ...

Loop a
Guys I am programming up a macro in Excel, I was just wondering how to loop the range function. Whereby I would like to somehow put a variable in the function Range, for a set column, eg. dim i as integer dim c(0) as string i=0 for i =117 to 200 c(0) = Range("C" & i) ' This is the line that doesn't work msgbox c(0) 'for simplicity next i Thanks No need for a loop Dim c c = Application.Transpose(Range("C117:C200")) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ben" <duivesteyn@...

Access Program Only Looping Part Way Through Outlook Inbox
Hello Outlook Group: I am developing an Access application that reads through the Inbox (both read and unread mail), and under certain conditions, adds the mail information to an Access database. When I am done looking at the mail item, I file it one one of two folders: either REJECTS or SAVED MAIL. I'm using POP3. The problem is that it only loops about half-way through the inbox and exits BEFORE all the mail in the inbox folder is processed. I have two sub-folders under the inbox, one call REJECTS and the other SAVED MAIL. If I do a ? InboxItems.COUNT, the count o...

Update a text box on a userform
I am not sure if this can be done, but it would be great if i could ge this to work.... I have 3 worksheets....Named SALES - CONTAINS MY USERFORM DATA - HOLD CORE DATA LONDON - DISPLAYS SALES FIGURE what i am trying to do is basically on the sales sheet, when iclick th new sales button at the top of the page, a userform will pop up. i want to be able to fill out the form, and when i have chosen the mak and model of car i want the text box to look display the recommende selling price for that car depending on the details in the data sheet. Is this possible please. if so how... cheers A...

Loop to compare and delete or leave
Using Excel 97 on WINNT. I regularly receive a report and I need to identify if something ne has appeared on the latest report. There are 2 identifiers I need t use to decide whether to delete the compared row of info or keep it. What I do is open the most recent report (call it ReportNew,xls for th purposes of my question) and save it under a new name (Comparison.xls) I then open the previous report (ReportOld.xls) to begin m comparison. Up to this point, I'm cruising on automatic but from her on in, I go to manual because I don't know how to write a loop to hel me automate the next ...

loop through rows
hi i have a worksheet that contains all the days of the year on each row in the column i have employee names i am creating a vacation tracker making sure that i have enought employees on shift i have counted the number of blank cells in a range(s8) if the value is different from 8 i want to lock the range of cells leaving the cell with a value in it unlocked i want to do this to stop more than one employee having vaction same day but allowing the employee who has a vacation to change his/her mind i have the following code that locks a range of cell How can i adapt this to 1. loop throu...

Looping through records and creating ID field
Hi there - I'm doing a database that will need to link with a Paradox database. To accomplish this I need to make a certain field in Access. I basically have a table that contains a persons first name and surname. I need to create another field called ID Make that takes the first six characters of the first name, adds in a hyphen and then adds in the first character of the surname. I can do this easily with a query like: Left([First Name],6) & "-" & Left([Surname],1) For example the name William Gates would be turned into: Willia-G Now here comes ...

for loops
I am trying to create a macro from scratch that can tell me what value is the maximum value in a column of values and then what the value is in the cell to the left of that max value. I thought a for loop would work for the max value part but i dont know how to get it to do that last part. Any help would be great, I am not good at programming. you don't really need a macro to do what you're describing. assuming your potential max value is in column B this formula would return the max: =MAX(B:B) and assuming the max value cannot occur in the column more than once,...

loop innner loop
I have this:- PN AGing 1111-1111 10 1111-1111 20 2222-2222 5 2222-2222 3 In this case i would want to select 1111-1111 with aging 10, and for 2222- 2222 the aging will be 5... I have a recset,but do not know how to continue... mySQL = "SELECT Wardboard.PartNumber, SupplyOrderDueDate, DateToCome FROM Wardboard " myrecset.Open mySQL Do Until myrecset.EOF hope someone willing to guide me on... -- Message posted via http://www.accessmonster.com ...

combobox list size changing(help needed)
hello, I am using a combo box control in my dialog box. I am showing a list of some parameters in the drop down list of the box.What i wanted to know is whether i can change the size of the list at run time and delete some parameters from the list.Like if user inputs 5 in a edit box then only 5 parameters will be availlable for him in the drop down list of the box( for him to choose further). Is drawItem and measure item functions of any use in this regard?It will be fine even if the parameters are not removed from the list but disabled so that user cant select them thanks, ...

Displaying Panel After Timer Loop
Good day. I have asp.net page in vb.net that gets set of photos records from a database table (paths to photos). Loads that data into session datatable, divide into parts and display each part at timer interval. For eg. every 7 seconds display first 5 (of 20) photos in asp.net datagrid, then next set of 5, then next set of 5 photos, etcetera. When all 20 photos have been displayed (paged) I want to show new panel for 7 seconds (pnlWarning). Hide pnlViewPhotos and show pnlWarning. Then, start loop again and start displaying paged photos again (in pnlViewPhotos). Then after 2...

loop
hi, i have the following: --****************************************** --make temp table --****************************************** CREATE TABLE #DCLNS2 ( B_DATE DATETIME, ACCT INT, LTR_CODE char(50), INV char(5) ) INSERT INTO #DCLNS2 ("B_DATE","acct","LTR_CODE","INV") (SELECT * FROM dcl_lts_CSV) --****************************************** --END of make temp table --****************************************** --****************************************** --select distinct B DATES from #DCLNS --*********...

Loop
Hi I have come accross this code on the news group which i would like to try and alter Sub WhosWho() Dim SourceSheet As Worksheet Dim xCell As Range, CurrentDoctor As String 'WHERE IS THE INFO? Set SourceSheet = Worksheets("sheet2") Application.ScreenUpdating = False SourceSheet.Copy After:=SourceSheet With ActiveSheet .Rows(1).ClearContents Set xCell = [a2] 'DRS NAMES MUST BE SORTED; USE FOLLOWING LINE IF NECESSARY xCell.CurrentRegion.Sort key1:=xCell, key2:=xCell.Offset(0, 1) Do While xCell <> "" With ...

Customized Userform
Is there a way to make userform with: 1. Different shape than default rectangular shape? 2. No title bar (as if the whole form lokks like, say, comman button)? 3. Location start from the top-left corner of A1 cell -- Message posted from http://www.ExcelForum.com 1. I don't think so. 2. There may have been posts that used some API calls that did this. You could search google to see if there are many hits. Here's one by Stratos Malasiotis: http://groups.google.com/groups?threadm=39EDCFB3.2FAA607A%40iti.gr 3. See Chip Pearson's site: http://www.cpearson.com/excel/FormPositio...

data validation vs combobox
hello there excel gurus.I am largely self taught in excel and can write straightforward macros and have a pretty good grasp of some of the functions.I have always used form comboboxes for my pull down lists.I notice that you seem to prefer the data validation list,pros and cons.?I like that the fom combo box is always there. -- paul remove nospam for email addy! Paul: There's bunches of companies who don't let their users run macros, for one. For another, Data Validation is, IMHO, much easier to show someone how to do. Comboboxes ALWAYS show their dropdown arrow. Comboboxes ar...

caught in loop
1st screen - Configuring updates stage 3 of 3 0 percent complete. do not shut off computer. 2nd screen - Shutting down. Then it loops over and over. I can't stop it. Please help. wolrah44 wrote: > 1st screen - Configuring updates stage 3 of 3 0 percent complete. > do not shut off computer. > 2nd screen - Shutting down. Then it loops over and over. I can't > stop it. Please help. Operating System? Service Pack? Safe Mode work? System Restore? -- Shenan Stanley MS-MVP -- How To Ask Questions The Smart Way http://www.catb.org/~esr/faqs/smart-q...

A looping condition was detected
Good morning. I have Exchange 2003. Lately we have had a lot of failed delivery notifications. The last one put an error in the application logs that said "A looping condition was detected" (3017). When I clicked on the link for more help I was told: "If you have multiple SMTP virtual servers configured on your Exchange server, make sure that they are configured with a unique incoming port and that the outgoing SMTP port configuration is valid. Make sure that all of the connectors are correctly configured. For example, make sure that no connectors have the address sp...

lists not populating userform
I got help with this code from Wouter. It worked great. Then I replaced variable and object names with less generic names that follow my standard naming schema. And lost the connection between the fields and the list boxes! Code now is: Private Sub cboCode_Change() Dim rCodes As Range cboFacilities.Clear For Each rCodes In Range("codeList").Cells If rCodes.Text = cboCode.Text Then cboFacilities.AddItem rCodes.Offset(0, 1).Text End If Next End Sub Private Sub usfFacilNameID_Activate() Dim rCodes As Range Dim iCodes As Integer Dim bCodes ...

Excel macro "loop"
To avoid several hand mader starts (400 x) of a macro I want to edit the VSB macro in a loop. I am not familiar with VSB but do have some experiance with other database languages. What is the exact way / commands to write down (edit) the macro? Someting like this. --------------------------------------------------------------- Start loop if n < 401 n = n+1 {a macro workaround with move, copy and past} endif end loop --------------------------------------------------------------- -- Vr.groet - regards, L´┐Żon Obers The code is: Do While n < 401 n = n ...

Why CListctrl block message loop?
Hi all, I encounter a problem in my project.below is the detail information: I create a single-documentview project ,and create a listctrl on the view. At the same time,I create a thread which call PostThreadMessage to post message to the main thread. In CMyPorjectApp::PreTranslateMessage(MSG* pMsg),I write a TRACE macro to output the message sent from the thread. Everything looks fine except when I press left mouse button on the listctrl and hold on.When I press the left button on the listctrl,the PreTranslateMessage() will not be called anymore and all the messages sent from the t...