Macro save data to then load new data?

Posted on 2014-07-16
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


    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

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now