Referencing a ComboBox in VBA

Hi,

I am using Excel 2007.  I want to run some code in my Workbook_Open() Sub 
that fills a combo box on one of my Worksheets (I will call it myCombo on 
mySheet).  I tried the following code and it did not work:

myCombo.Clear
myCombo.AddItem "Item One"
myCombo.AddItem "Item Two"
myCombo.AddItem "Item Three"

I assume that the problem is that within the Workbook_Open() Sub, the 
reference to myCombo is not valid.  Can anyone tell me if/how I can get a 
valid reference to myCombo (on mySheet)?

Thanks in advance,
Paul Kraemer



-- 
Paul Kraemer
0
Utf
2/9/2010 6:28:05 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
3893 Views

Similar Articles

[PageSpeed] 23

One way:

Option Explicit
Private Sub Workbook_Open()
    Dim wks As Worksheet

    Set wks = Me.Worksheets("mySheet")

    With wks.OLEObjects("myCombo").Object
       .Clear
       .AddItem "item one"
       .AddItem "item 2"
       .AddItem "3"
    End With
End Sub



Paul Kraemer wrote:
> 
> Hi,
> 
> I am using Excel 2007.  I want to run some code in my Workbook_Open() Sub
> that fills a combo box on one of my Worksheets (I will call it myCombo on
> mySheet).  I tried the following code and it did not work:
> 
> myCombo.Clear
> myCombo.AddItem "Item One"
> myCombo.AddItem "Item Two"
> myCombo.AddItem "Item Three"
> 
> I assume that the problem is that within the Workbook_Open() Sub, the
> reference to myCombo is not valid.  Can anyone tell me if/how I can get a
> valid reference to myCombo (on mySheet)?
> 
> Thanks in advance,
> Paul Kraemer
> 
> --
> Paul Kraemer

-- 

Dave Peterson
0
Dave
2/9/2010 6:49:39 PM
You need to reference the worksheet that the combobox is on:

Worksheets(1).myCombo.Clear
worksheets(1).myCoimbo.AddItem "Item One"
etc.



"Paul Kraemer" <PaulKraemer@discussions.microsoft.com> wrote in message 
news:102FCEA6-B8C0-4CD3-AE01-42BE974FB937@microsoft.com...
> Hi,
>
> I am using Excel 2007.  I want to run some code in my Workbook_Open() Sub
> that fills a combo box on one of my Worksheets (I will call it myCombo on
> mySheet).  I tried the following code and it did not work:
>
> myCombo.Clear
> myCombo.AddItem "Item One"
> myCombo.AddItem "Item Two"
> myCombo.AddItem "Item Three"
>
> I assume that the problem is that within the Workbook_Open() Sub, the
> reference to myCombo is not valid.  Can anyone tell me if/how I can get a
> valid reference to myCombo (on mySheet)?
>
> Thanks in advance,
> Paul Kraemer
>
>
>
> -- 
> Paul Kraemer 


0
JLGWhiz
2/9/2010 6:59:07 PM
Give this a try.  This should work if it is an ActiveX dropdown box.  Hope 
this helps!  If so, let me know, click "YES" below.

Private Sub Workbook_Open()

    With Sheets("Sheet1").myCombo
        .Clear
        .AddItem "Item One"
        .AddItem "Item Two"
        .AddItem "Item Three"
    End With
    
End Sub
-- 
Cheers,
Ryan


"Paul Kraemer" wrote:

> Hi,
> 
> I am using Excel 2007.  I want to run some code in my Workbook_Open() Sub 
> that fills a combo box on one of my Worksheets (I will call it myCombo on 
> mySheet).  I tried the following code and it did not work:
> 
> myCombo.Clear
> myCombo.AddItem "Item One"
> myCombo.AddItem "Item Two"
> myCombo.AddItem "Item Three"
> 
> I assume that the problem is that within the Workbook_Open() Sub, the 
> reference to myCombo is not valid.  Can anyone tell me if/how I can get a 
> valid reference to myCombo (on mySheet)?
> 
> Thanks in advance,
> Paul Kraemer
> 
> 
> 
> -- 
> Paul Kraemer
0
Utf
2/9/2010 7:34:01 PM
Reply:

Similar Artilces:

Referencing merged cells
I'm having trouble referencing a merged cell in another workbook. Say I merge cells A1 to C1 in workbook 1. When I make this the active cell, the Name Box says "A1". When I go to another workbook (say, workbook 2), go to cell B1, type "=" and then go to the merged cell and select it, I get 'workbook1'!$A$1:$C$1 as the formula and, of course, the "#VALUE" error. I've successfully tried typing "=sum(" without the quotes hen clicking on the contents of the cell and then adding the ")" and it works O.K. but there should be an easi...

Protect Sheet vs. VBA Code
I have a data entry sheet which has VBA code to automatically place some formulas in certain cells. I wish to lock certain columns, then protect the sheet prior to turning it over to a user. There are some columns which I wish to protect which are also automatically filled by the VBA code. With these columns locked, and the sheet protected, I get an error when the VBA code attempts to fill the cells in these columns. Is there a way to let VBA perform and still have these columns protected? Thanks. --- Message posted from http://www.ExcelForum.com/ Hi Steveski! Use: Worksheets(&quo...

Referencing another field if result is N/A
Hello, I have a situation where I am referencing data on another workbook. The reason behind this is the data in Column H can be entered incorrectly and often is. I am able to prevent this going forward but can not do so for the items already entered. I enter a formula via VBA, however, sometimes the result will be #N/A and if so I would prefer to leave the old value and just highlight it that it needs to be verified by hand. I am not sure if I can just "leave" the value as it has pretty much already been replaced. Due to this, I copy the column to a different colum...

vba function #2
Hell i've the following data Année V1 V2 V3 Nationa 1992 14805 10 1993 16521 2640 120 1994 17839 2142 117 1995 19878 1236 1207 136 1996 12456 1024 14 1997 14526 1563 1274 15 jI want to calculate an "'indice" with vba example: to obtain these news value Ann...

Help With Simple Combobox Programming
I have a combo box on sheet1. When the user clicks on the combo box the first time ( it gets focus ) It should add/load all items from sheet2 column A. Now when the user selects from the combobox that item should be copied/placed on sheet1.A5 similarly the next item selected in the combo box should be placed below A6 and so on How can this be accomplished with code thx Hi, Use code like that : Private Sub cboIn_Click() Dim intR As Integer intR = Range("a4").CurrentRegion.Rows.Count Range("a4").Offset(intR, 0).Value = cboIn.Value End Sub Priv...

Inputbox in VBA
Hello, I have the following VBA macro: " If WorksheetFunction.CountIf(Range("E394:E709"), Range("E900")) <> 1 Then Range("E" & i).EntireRow.Delete Else Range("L" & i).Value = ans End If End If Next i" I would like to know if it is possible to have an inputbox in VBA that would give me the option to the change this range "(Range("E394:E709"). Jeff Jeff, Something along the lines of Set oRng = Range("E394:E709"), On Error Resume Next Set oRng2 = In putbox("Select a range or cancel...

Worksheet doesn't permit F4 absolute referencing tool in formulas
I've never seen this before in a worksheet and can't find a Tool-Option that would change it. You can of course type the dollar signs in, but the F4 key won't toggle. Sure it's something simple, any help appreciated! -- ronthedog ------------------------------------------------------------------------ ronthedog's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26504 View this thread: http://www.excelforum.com/showthread.php?threadid=397725 ...

VBA cpy & Paste
Hi all, Can I, using VBA, copy range A1:G1 and paste only the content of the first three cells (A1:C1) to, let's say, A2:C2? Thanx for your help, Uziel No, but you could just copy A1:C1 to start. uziel_9@yahoo.com wrote: > > Hi all, > > Can I, using VBA, copy range A1:G1 and paste only the content of > the first three cells (A1:C1) to, let's say, A2:C2? > > Thanx for your help, > Uziel -- Dave Peterson Thanx Dave, I'm trying to use an already existing code(First code) that copy A1:G1 and would like to, in one occasion (Second code): Paste the al...

Using a VBA forms text box to show the filename path
Hi, I want that when i launch my userform the activesheet saved location i shown in a text box (textbox1) How can this be done? cheers stev -- Message posted from http://www.ExcelForum.com Do you mean that you want the saved location of the workbook to b displayed? If so, use the following. TEXTBOX1.TEXT = ACTIVEWORKBOOK.FULLNAM Rolli -- Message posted from http://www.ExcelForum.com ...

Populate combobox
Hello Why doesn't this populate my ActiveX combobox? Private Sub ComboBox1_Change() Dim row As Integer ' Make sure the RowSource property is empty Sheet1!ComboBox1.RowSource = "" Sheet1!ComboBox1.RowSource = "Sheet1!A1:A12" ' Add some items to the ActiveX combobox For row = 1 To 12 Sheet1!ComboBox1.AddItem Sheets("Sheet1").Cells(row, 1) Next row End Sub Change the exclamation point (!) to a period (.) after Sheet1 everywhere except in the row source reference that is within the quote marks. &quo...

Open Files listed in cells using VBA
I have a list of filenames starting in cell A1 and going down the column. The list can be of varying length. ie Book1.xls Book2.xls book3.xls I need to open each of these books in turn, and perform a macro (this part of the code is already written). What is the best method for opening each of these files in turn and performing the macro? thanks --- Message posted from http://www.ExcelForum.com/ Hi assuming these files are all in the same directory try something like the following: sub foo() dim source_wbk as workbook dim source_wks as worksheet dim source_rng as range dim cell as rang...

This workbook is currently referenced by another workbook and cannot be closed.
The message is given when I try to close a workbook / kill Microsoft Excel. I'm using Microsoft Excel 2002 / XP. The spreadsheet includes a reference to another sheet that someone else referenced in another message here. I put the AddIn sheet in via: Tools, AddIns..., Analysis ToolPack - VBA. I've not referenced any function or procedure in the add-in. I get a Triangular Warning / Error ? It's got a graphic of a Yellow Triangle with an Exclamation Mark inside. The subject is the exact text of the warning. A Microsoft Search on the subject yielded nothing. Google yielded one ...

multiple select list boxes on form
I'm reposting this because I have been working for over three weeks trying to develop the VBA code and was hoping to stay in that vicinity. My form has multiple-select list boxes (both text and numbers) that contain customer contact info, company revenue, and sales relationships. How do I loop the code so that the form displays (which will eventually be exported to Excel) only the records based on the multiple criteria selection within each box? Any help you can offer is appreciated. I can get the code to work for one multiple-select dropdown filter, and I can get the code to work for...

referencing the cell above
Hi I have a kin a formula wich gets the value from the cell above and then add some. What I get is an list of values incrementing. When I delete one row in this list, all cells below 'crashes' with #REF!. What I need is that for the formulas to always look one cell up, even if I delete a row. Now it seems that if I delete Row8, then when Row9 become Row8 it reference to it self and then ov course goes bananas... Hope I made myself understandable ;-) Any ideas folks? -------- stuhag --------- Instead of using say =A19, use =OFFSET(A20,-1,0). This would go in A20, and as it does...

Indirect Range Referencing
Hello, I work in a paper mill. I am building a spreadsheet with several charts. I have a wide sheet of paper coming off of a paper machine of varying widths. This paper is wound up on a "reel".I have a scanner that measures the weight of the paper on the reel, and each scan gives me a 600 point array. I can calculate where the edges of the paper lie within the array, no problem, and plot the whole thing very nicely. The problem is that the paper then goes into a re-winder, where the wide sheet is cut into smaller sheets, that make up "rolls". I can calculate the sta...

Formula Changes when data entered in referenced range
I have 5 formulas in a worksheet that refer to a range. When data is entered in the range, the formulas adjust to exclude the cells with data. Range B5:B211 When data is entered in B5, range in the formulas becomes B6:B212. The workbook has nearly 200 worksheets with the same formulas. I'm working in Excel 2003. Does anybody have any idea what's going on????? Might help if you include the formulae that you are using. Regards Trevor "mac849" <mac849@discussions.microsoft.com> wrote in message news:33BDD2CD-C071-4DAE-A821-4967E94C16A3@microsoft.com... >...

How can i jump to a cell referenced in a formula?
Anyone know of a way to jump to a cell referenced in a formula, when it is not the first reference...e.g. Formula bar reads "=D5+G5" CTRL+{ will take me to D5 but is there a way to get to G5 easily? Especially helpful in long formulas and off sheet links. thanks Ctrl-Shift-{ than press tab. HTH, Bernie MS Excel MVP "PowerExcelWannabe" <PowerExcelWannabe@discussions.microsoft.com> wrote in message news:88A4B2CC-C90E-4918-8DB7-04C09B6F72AC@microsoft.com... > Anyone know of a way to jump to a cell referenced in a formula, when it is > not the first referen...

vba sourcecode to convert pdf to xls
Hello, Does anybody know a home page where an example of a vba source code is listed, which enables to convert a pdf file (with tables) into an xls file. I would need some ideas how to do this, in order to write my one little vba programme. Thanks in advance Rudolfo You would normally use some OCR software to achieve this - the PDF file can be read by the software and turned into editable text - or Adobe Acrobat full version (not just the Reader). I'm not sure how you would do it with VBA. Hope this helps. Pete ro.koehler@gmx.de wrote: > Hello, > > Does anybody know a hom...

MS Excel VBA Interview Questions & Answers
Hi Friends, Click here --> http://www.excelitems.com/2010/12/excel-vba-interview-questions.html These Excel VBA Interview questions are being posted keeping in mind that reader is aware of working with VBA, have some programming and MS Excel background and is aware of terminologies. This question bank is helpful for both Interviewee and Interviewer as it provides a quick channel of questions and answers covering major topics of Excel and VBA for quick revision. ________________________________________ Thanks & Regards Ashish Jain (Microsoft Certified Application Specialist) (Microso...

Problem with referencing a hidden column
The code below works great, that is until I hide column Q. I do not want the data in column Q to me visible on the worksheet. Is there any way around this problem? TextBox100.Value = Range("Q4").Text TextBox200.Value = Range("Q49").Text TextBox300.Value = Range("Q94").Text TextBox400.Value = Range("Q139").Text TextBox500.Value = Range("Q184").Text TextBox600.Value = Range("Q229").Text TextBox100.Value = Range("Q4") -- Regards Dave Hawley www.ozgrid.com "ordnance1" <ordnance1@comcast...

ComboBox Lookup Problems
Right now I have a Combobox that you can select a device and it will bring up information about that device and by hitting an edit button you can in turn edit this information. The way this happens is the combobox becomes hidden and a text box appears and then I requery the combobox incase any changes were made when you hit the done editing button. This works fine for devices already in the database. But say I have device A and I want to add device B I click edit and add device B to the DB when I am done editing I click Done Editing and the combobox re-appear. The problem is the combo...

referencing
I don't understand why we use cell referencing at all. Can anybody tell me what is the different between these references $A1, A$1, and $A$1 or exactly what they do. Thanks Take a look at the "The difference between relative and absolute references" topic in the "About cell and range references" topic in XL Help. Essentially, When copying/dragging/filling formulas, putting a $ in front of the column character(s) preserves that column in the copied cells. Likewise a $ in front of the row number. Put these formulae in the indicated cells: B1: =$A1 C1:...

Excel VBA Column
Hi, This is about Excel VBA. How do I detect if the active cell is in Column A or not ? My pseudo code is like this: If any active cell is selected within Column A then Process normally else 'current active cell is in other columns MsgBox "You are not in Column A", 16 please help. thanks. Regards, Magix If Activecell.Column = 1 Then ... -- HTH RP (remove nothere from the email address if mailing direct) "magix" <magix@asia.com> wrote in message news:435fa580$1_2@news.tm.net.my... > Hi, > This is about Excel VBA. > How do ...

Simple but repetitive use of combobox
Hi, my environment is OS WinXP and MSoffice2003, I am fairly novice with VBA, but not completely. I am a secretary in a dental clinic where each patient receives a diagnostic code. I am creating an EXCEL workbook with 31 worksheets (one for each day of the month), and an end of month total (worksheet 32). Each of the blank daily worksheets will have activex comboboxes in some blank columns (10 patients or so). Each time a patient name is typed, a corresponding code will be selected from a drop down list on that row (blank rows should return a value of zero). At the end of t...

VBA and SQL Connections
We're 2 weeks past the upgrade to Dynamics GP V10.0 and we have been observing somthing that is bothering us. Our environment has a lot of Modifier and VBA customizations. The modifications are mostly about pulling data from SQL shadow tables and displaying it (the data) in Modified forms. We've been monitoring the number of SQL connnections that users are making, since pre-GP10 most users would have between 2 and 6 connections. Now we are observing users with HUNDREDS of connections open. I've tested in my DEV environment and on a given screen, each time the data-pulli...