+ Reply to Thread
Results 1 to 5 of 5

Can I use one fill code to fill comboboxes in multiple forms?

  1. #1
    Registered User
    Join Date
    11-15-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    6

    Can I use one fill code to fill comboboxes in multiple forms?

    Hi, I'm new to Excel Forum and new to vba.

    I love excel and want to take advantage of more of its capabilities. I am very unfamiliar with vba vocabulary but have managed, through the aid of several forum posts, to copy/paste and modify codes to work for my needs. I have recently learned about the idea of modules, this idea that I can continue to call the same operation into multiple modules/forms, rather than having the entire operation re-written in each module/form. That being said, I have several forms which re-use various comboboxes, and I currently have the whole fill code for each combobox written in each form's code, and was wondering about the possibility of writing a fill code by itself, and then calling it to a form code. For two reasons:To make the form code look cleaner, and so that if an adjustment has to be made I don't I have to make it in every form's code.

    Thanks for your help!

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Can I use one fill code to fill comboboxes in multiple forms?

    Yes - that's the way to do it - put the code in a standard module and then invoke it by name in each form!
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    11-16-2012
    Location
    Crownpoint, New Mexico
    MS-Off Ver
    All.
    Posts
    59

    Re: Can I use one fill code to fill comboboxes in multiple forms?

    It really is a simple as xladept makes it sound. As long as it's not a function referencing a control (button etc.) you can simply copy and paste it into a module and it will work.

    Just take out Private.

    Sub Macro1()
    With Selection
    .Value = xlOff
    .LinkedCell = "$B$9"
    .Display3DShading = False
    End With
    End Sub

    Called with
    Macro1
    Please click * to add to my reputation!

  4. #4
    Registered User
    Join Date
    11-15-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Can I use one fill code to fill comboboxes in multiple forms?

    Somehow I seem to be making it more complicated than necessary, I have attached a simple spreadsheet with two buttons, "Easy" and "Learning". The "Easy" button opens a form with a combobox filled the redundant way which I know how. The "Learning" button ought to open a similar form, but with the combobox filled using the new (new to me anyway) sub method. I continue to get "Run-time error '424': Object required". If you guys could take a look at this and teach me the error of my ways that would be great. I'm afraid I'm going to embarrass myself with something terribly simple.

    Thanks so much!

    ComboBoxFill.xlsm

  5. #5
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Can I use one fill code to fill comboboxes in multiple forms?

    Hi 0612man,

    Try this setup:

    Please Login or Register  to view this content.
    And in the Initialize event for both forms:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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