+ Reply to Thread
Results 1 to 4 of 4

Macro to detect selection in 2 separate list boxes and react accordingly.

  1. #1
    Registered User
    Join Date
    08-26-2012
    Location
    Mayberry, NC
    MS-Off Ver
    Excel 2003
    Posts
    14

    Exclamation Macro to detect selection in 2 separate list boxes and react accordingly.

    I was just handed a project that the previous person was unable to complete, and considering I am an amateur at best in Macro/VBA, I really need some help. What this macro needs to do is when clicking on a submit button, it should recognize the two entries that have been selected in seperate list boxes, i.e. Department and Shift. Then based off the selection, it will copy the Totals cell into a different workbook which will be on a different computer but networked to the one being used. I am posting the code that I have no idea what is attempting to do, and attaching the workbook. Bossman forgot to give me directory listing for the other computers file, so I will be adding that in the morning. If anyone could help, it would be greatly appreciated.

    Please Login or Register  to view this content.
    MONEY COUNT.XLS

    P.S. Also, not sure if the code above is the submit code he was writing, all I know is when this code short cut key is entered, it goes nuts and the whole spreadsheet gets moved all around and then ends in error.
    Last edited by Kistler; 08-28-2012 at 01:45 AM. Reason: addendum

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Macro to detect selection in 2 separate list boxes and react accordingly.

    Hi, Kistler,

    Then based off the selection, it will copy the Totals cell into a different workbook which will be on a different computer but networked to the one being used.
    Is that workbook opened in the instance of Excel or does it need to be opened just for the copying? Where shall that data be copied? Is it one sheet conitnuous with date/time stamp and values of CBs and Value total? Are there more sheets regarding the departments?

    First thing in your Submit-Procedure for me would be to check if both Comboboxes show values:

    Please Login or Register  to view this content.
    I choose Exit Sub here but I would find it more informative to place a Message Box telling the user to make a choice in Department or Shift.

    At the end (with the clearing of the ComboBoxes) I would reset the values for the ComboBoxes as well to None.

    Ciao,
    Holger

  3. #3
    Registered User
    Join Date
    08-26-2012
    Location
    Mayberry, NC
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Macro to detect selection in 2 separate list boxes and react accordingly.

    The worksheet that the "Totals" are being copied to only needs to be opened to copy the information to it, and then save and close. It will be worked on by a different department. The sheet will be cleared each day, so date/time stamp is not necessary. Still waiting on the directory reference where this will be copied to, just know it is somewhere on the network. Thanks for the piece of code though.

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Macro to detect selection in 2 separate list boxes and react accordingly.

    Hi, Kistler,

    itīs not really obvious to me if you need furter assistance or if you most hopefully are archieve what you want by yourslf. If you need more information please feel free to ask. Trial and error is a very good way to learn things - itīs a very long route as well (and sometimes a very troublesome). Maybe you can start by recording a macro, look at that code, copy it and start to work that code over.

    In the example code you posted there are a lot of Selects. Most of these can be taken out as this is an issue due to the macro recorder: activate first, then show the action to do. Same goes with the ActiveWindow.ScrollColumn = xx. I havenīt looked to deep into that code by now, itīs just what comes to mind when glimpsing at it.

    Ciao,
    Holger

+ 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