当前位置: 动力学知识库 > 问答 > 编程问答 >

vba - Excel Macro not working when I add a new Sub with same code

问题描述:

I am not going to send you the entire code, because it is just a block of code repeated a lot... Below is the over all gist of the code, the line of code is repeated multiple times with the "Sheets" & "Shapes" changing when necessary. What it is doing is I have a list box(which has a list of items) every time you click a specific item, there are about 30 text boxes that populate a specific sub-item that is related to the list box item.

The problem is, it is working for the Sub ListBox1_Change Macro for one tab, but when I try to do this for a second tab in the same Excel file it says there is a parameter error. However, and this is the interesting part, when I actually click on the text box the parameter error pops up. I click okay, then it populates.

The code works, but it isn't working when I try to do it on a second tab. I have tried combining them into one Sub, but that didn't work at all. So I broke them out into two separate Subs. Any input would be nice.

Sub ListBox1_Change()

Handle_Change Me, "List Box 1", "TextBox1", "W"

Handle_Change Me, "List Box 1", "TextBox2", "X"

Handle_Change Me, "List Box 1", "TextBox3", "Y"

Handle_Change Me, "List Box 1", "TextBox4", "Z"

Handle_Change Me, "List Box 1", "TextBox5", "AA"

Handle_Change Me, "List Box 1", "TextBox6, "AB"

End Sub

Along with the Handle that is in the Module

Sub Handle_Change(sht As Worksheet, lbName, tbName, colAddr)

Dim idx As Long, lb As msforms.ListBox

Set lb = sht.Shapes(lbName).OLEFormat.Object.Object

idx = lb.ListIndex

If idx <> -1 Then

sht.Shapes(tbName).OLEFormat.Object.Object.Text = _

sht.Range(colAddr & idx + 1).Value

End If

End Sub

网友答案:

You should refactor your code before you write any more.

EDIT: made a few changes after testing:

'These go in the relvant worksheet code module
Sub ListBox1_Change()
    Handle_Change Me, "ListBox1", "TextBox1", "B"
End Sub

Sub ListBox2_Change()
    Handle_Change Me, "ListBox2", "TextBox2", "C"
End Sub    


'This goes in a *regular* code module
Sub Handle_Change(sht As Worksheet, lbName, tbName, colAddr)

    Dim idx As Long, lb   As msforms.ListBox

    Set lb = sht.Shapes(lbName).OLEFormat.Object.Object

    idx = lb.ListIndex
    If idx <> -1 Then
        sht.Shapes(tbName).OLEFormat.Object.Object.Text = _
                         sht.Range(colAddr & idx + 1).Value
    End If

End Sub
网友答案:

Based on the discussion here, and the link to this MSKB article, it sounds like the error you're seeing may be a result of the underscores in your macro names. This only seems to apply to Excel '97, though. If that's the version you're on, take out the underscores and see if the error goes away.

分享给朋友:
您可能感兴趣的文章:
随机阅读: