[Last Call] Learn how to a build a cloud-first strategyRegister Now


Macro save data to then load new data?

Posted on 2014-07-16
Medium Priority
Last Modified: 2014-07-18

The list box on sheet “Step 3 - Define Causal Factors” is where users select the Event to work on. The system is designed to handle a maximum of five events. In the upper right had corner of sheet “Step 3 - Define Causal Factors” there is a series of LOAD and SAVE buttons.

The macros assigned to the buttons either load all the data for the selected event or save all the data for the selected event.
The problem is when a user the list box to select a different event. The macro does not save the data to the existing event before changing to the new event. There are 5 load macros and 5 save events.

How can I solve this problem?
Question by:cssc1
1 Comment
LVL 10

Accepted Solution

FamousMortimer earned 2000 total points
ID: 40205017

See attached...  I changed the listbox form control to an activex listbox control which has events that are can be handled.  In this example i added the code below to sheet 'Step 3 - Define Causal Factors'.  Basically, it triggers when a mouse button is pressed down (so before the mouse click is complete) and uses a case statement based on the index of the item clicked to determine which save to perform.

'this will trigger before the selection changes
Private Sub ListBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    'make sure it is the left mouse button that is clicked
    If Button = vbKeyLButton Then
        Select Case ListBox1.ListIndex
            'the index of the list items are zero-based so the first item is index 0
            Case 0
                Call Save1
            Case 1
                Call Save2
            Case 2
                Call Save3
            Case 3
                Call Save4
            Case 4
                Call Save5
        End Select
    End If
End Sub

Open in new window


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question