Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
811 views
in Technique[技术] by (71.8m points)

excel VBA code not working

I have a excel sheet with a VBA code as follows

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 And Target.Column = 5 Then
        Dim iRet As Integer
        If Not IsEmpty(Range("AZ1").Value) Then
            iRet = MsgBox("You have already selectd a Size Template", _
                          vbOKOnly, "Select Size Template")
            Exit Sub
        End If

        Dim arr As Variant
        arr = Split(Target, ",")
        Range("R14:AZ14").ClearContents
        Range("R14:AZ14").NumberFormat = "@"
        Range("R14", Cells(14, UBound(arr) + 18)) = WorksheetFunction.Transpose( _
                                        WorksheetFunction.Transpose(arr))
        Range("AZ1").Value2 = Target
    End If
End Sub

I saved the excel file as .xlsm(macro enabled excel file) and opend in another.This code works really fine in my machine. BUt not in any other machine. I enabled the marco and allowed the Trust acess to the VBA ojbect model. CAn anybody figure out the issue here . Excel versions are also same in both mahcines enter image description here

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

I want to elaborate a bit the comment of mine which was correct suggestion.

First, let me repeat that- you need to switch on events in this way

Application.EnableEvents = true

which you can be run once in Immediate Window in VBA/IDE Editor. Now we know that was it!

Second, if you decided to switch on events using any other subroutine (or event, which is however strange) please keep in mind that there could be some other subroutines, functions or add-ins which require events to be switched off. As long as you are not sure why events are not working you should keep them not working right after your macro doesn't need them any more. Therefore, my suggestion is to switch events off each time you will close your file. Therefore you could add this event to ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.EnableEvents = False
End Sub

Extra tip. The best option would be to read events status at the beginning, keep this information until you close your file. You could do it in the following steps:

A) declare public variables in your file

Public boEventsStatus as Boolean

B) read status when opening file (you need to figure it out where put this line of code)

boEventsStatus = Application.EnableEvents

C) switch on events as described at the beginning

D) use this BeforeClose event:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.EnableEvents = boEventsStatus
End Sub

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...