Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Why is my macro not running?

Posted on 2014-07-17
3
Medium Priority
?
334 Views
Last Modified: 2014-07-18
I have code on sheet "Step 3 - Define Causal Factors" that is attached to 'List Box 21" and it should run one of the 5 macros.

It is not working.

Please advice.

+++++++++++++++++++++++++++++++++++++++++++++++++

  Private Sub List_Box_21_Change()


      If List_Box_21_Change.ListIndex > -1 Then
          Select Case List_Box_21.Value
          Case "One": Run "Load_1"
          Case "Two": Run "Load_2"
         Case "Three": Run "Load_3"
          Case "Four": Run "Load_4"
          Case "Five": Run "Load_5"
End Select
    End If
    End Sub
Root-Cause-Analysis-Ver-17.xlsm
0
Comment
Question by:cssc1
  • 2
3 Comments
 
LVL 35

Assisted Solution

by:Norie
Norie earned 1336 total points
ID: 40203312
It's a form listbox, so the code for it should go in a standard module and you should assign it to the control.

Add a new module (Insert>Module) and paste this code in it.
Sub List_Box_21_Change()

    If Sheets("Step 3 - Define Causal Factors").ListBoxes("List Box 21").ListIndex > -1 Then
        Select Case Sheets("Step 3 - Define Causal Factors").ListBoxes("List Box 21").Value
            Case "One": Run "Load_1"
            Case "Two": Run "Load_2"
            Case "Three": Run "Load_3"
            Case "Four": Run "Load_4"
            Case "Five": Run "Load_5"
        End Select
    End If
    
End Sub

Open in new window

Then right click List Box 21, select Assign Macro, select List_Box_21 from the dialog and press OK.

The code should then run but it won't do anything as the values in the code don't match up with those in the listbox.
0
 
LVL 24

Assisted Solution

by:Ejgil Hedegaard
Ejgil Hedegaard earned 664 total points
ID: 40203330
The element is not an active-x listbox, but a formular control element.
When I right click on it, the name in the name box (upper left corner just above the sheet) is "Liste 21", but probably Excel translates to local name, so check to insert the correct list name in the macro.
The macro can not be Private, to be able to connect the element to the macro.
Then this macro works

Sub List_Box_21_Change()
    If [Liste 21].ListIndex > -1 Then
        Select Case [Liste 21].Value
            Case 1: Run "Load_1"
            Case 2: Run "Load_2"
            Case 3: Run "Load_3"
            Case 4: Run "Load_4"
            Case 5: Run "Load_5"
        End Select
    End If
End Sub

Open in new window

0
 
LVL 35

Accepted Solution

by:
Norie earned 1336 total points
ID: 40203378
Here's the workbook with the code updated.
Root-Cause-Analysis-Ver-17.xlsm
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

810 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