+ Reply to Thread
Results 1 to 4 of 4

Coding comboboxes to limit to list

  1. #1
    Registered User
    Join Date
    06-17-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    23

    Coding comboboxes to limit to list

    I am working with a project that has a series of workbooks to collect data from our various machine operators. They fill out a row of data, including a combobox for listing any problems during a particular hour. If there are multiple problems, they can click a checkbox and add an additional row. This adds a new combobox to the new row, but I can't seem to figure out how to make it a comboboxlist, type 2, which limits entries to the list in the drop-down. With these newly generated comboboxes, the user can enter anything, and we don't want that. In the existing comboboxes, I simply set the style property to 2 - comboboxlist, but I can't figure out how to apply that to the code.

    This is the code I have for the new comboboxes:

    PHP Code: 
        'To add combobox for problems
        Set x = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1")
        x.Name = cmboxName1
        
        x.Height = Rows(rowOffset).Height
        x.Width = Columns("G").Width
        x.Left = Columns("G").Left
        x.Top = Rows(rowOffset).Top
        x.Object.Font.Size = 10 
    Any help would be appreciated!

  2. #2
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Coding comboboxes to limit to list

    I use a UserForm to do pretty much the same thing you are doing. DO you have the list in certain cells on the same or another sheet? If so, you can set it in the properties window of the code. Lets say the cells you have the list on are in Sheet2 A1 through A10. In the properties window for the code for that ComboBox, next to "Row Source" type 'Sheet2'!A1:A10
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  3. #3
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,177

    Re: Coding comboboxes to limit to list

    Is this a form?
    you should use a form, and control things that way rather than creating more and more combo boxes on a sheet.
    the form allows users to enter data, then it can post this data to the form
    the combos can be visible or not
    or limit to the list using MatchRequired property.

  4. #4
    Registered User
    Join Date
    06-17-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Coding comboboxes to limit to list

    It's not a form, this is an inherited project, with about 20 different workbooks, one for every machine. They have been using them like this for several years, the operator enters their data every hour, then click a checkbox transfers the data to a separate workbook. Every day the workbooks are cleared, the new data is entered at the top, and the operators continue the process. It actually works pretty well, but the recent migration to 365 combined with network hiccups has brought some issues. I'm adding some checks to prevent double-posting...it will now look in the destination sheet to make sure the particular hour being submitted isn't already there, and I put in a check to make sure they aren't skipping hours, before they could enter data in any of the hourly slots at any time, now they have to go in the correct order. Some other things are in the works

    One thing that has always been a problem is this combobox problem; it was never set to restrict the options. I did that for the existing ones, it's just the newly generated ones that are allowing the user to type in options not on the list. And I'm not sure how to apply this...using the code from above, I don't know how to access the style property.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 02-18-2015, 02:02 PM
  2. Help! Entire Row Hidden - w/Two ComboBoxes - Doesn't Hide the ComboBoxes
    By ocnmel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-06-2015, 09:27 AM
  3. Limit replication of coding inside spreadsheet
    By PaulBas in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-16-2014, 04:01 PM
  4. [SOLVED] Calculating Time in Userform Textbox and Filtering Comboboxes Coding Help
    By hiddenupnorth in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-01-2012, 01:12 PM
  5. Add same list in multiple comboboxes
    By aman1234 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-07-2011, 04:41 AM
  6. Replies: 17
    Last Post: 10-22-2010, 04:20 AM
  7. coding to add values until a certain limit
    By dan2010 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-16-2010, 05:18 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1