Excel VBA does not run if other Excel spreadsheets are open

[Archer5]

Hi,
I have encountered the strangest error in Excel and I hope someone can help.

I have a form with a lot of VBA code to set defaults, hide rows, etc. I find that at times, if I have other spreadsheet open, the VBA code does not run. This means defaults do not get set and so on.

Has anyone come across this before and if so, what is the fix.
Thank you in advance.
Archer5

 

[nemmi69]

Can you show your code?

 

[Archer5]

Hi nemmi69,

The workbook has a lot of code, which part are you interested in?

Cheers

 

[nemmi69]

The part which does not run if another workbook is open. I am looking for how you reference a sheet in a workbook.

 

[Archer5]

Apologies for late reply. None of the code runs so it difficult to know what to give you. Sheets are referenced with either Sheet1.Range("D2").Value or Sheet1.Range("<named range>").Value

Here is a section o om of the code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim eanLength As Boolean '//used for testing length of ean
Dim shp
Dim tabCheck As Boolean
Dim checkForBOM As Boolean
'// use for upper case
Dim chgRng As Range
Dim c As Range
Dim cVal
Dim strRng As String, MergedRng As String '// used for calling AutoFitMergedCells
Dim chkForBOMAdd As Boolean '// used to workout if add to bom has been selected

On Error Resume Next
Call TurnOffFunctionality

'// clear item subClassification
If Target.Address = Sheet1.Range("txtItemClassification").Address Then
Application.EnableEvents = False
Target.Offset(2, 0).ClearContents
Target.Offset(2, 3).ClearContents
End If

'// hide/show rows for UPDATE/CREATE/DEACTIVATE
If Target.Address = Sheet1.Range("$D$4").Address And Sheet1.Range("txtStatus").Value <> "SUBMITTED" Then
If Sheet1.Range("txtReqType").Value = "CREATE" Or Sheet1.Range("txtReqType").Value = "" Then
Sheet1.Rows("10:16").EntireRow.Hidden = True
ElseIf Sheet1.Range("txtReqType").Value = "UPDATE" Then
Sheet1.Rows("10:16").EntireRow.Hidden = False
Sheet1.Range("C13:D13").Font.Color = vbBlack
Sheet1.cbx_Tab01.Enabled = True
ElseIf Sheet1.Range("txtReqType").Value = "DEACTIVATE" Then
Sheet1.Rows("10:16").EntireRow.Hidden = False
'// deactivate tab selection
Sheet1.Range("C13:D13").Font.Color = RGB(89, 89, 89)
Sheet1.cbx_Tab01.Enabled = False
End If
End If

 

[Norie]

Are you sure the code isn't running?

It could be running but not doing what you expected, or even running against one of the other workbooks.

PS Is the code located in Sheet1's module?

 

[offthelip]

I notice in the code you have posted that you have got application.enableevents=false. You don't seem to have an appication.enableevents=true
I suggest you check the status of this since this could cause your problem. and write yourself a subroutine for the personal.xlb file with;

sub resetevents
application.enableevents=true
end usb

 

[Archer5]

Hi,
Apologies for the delay in replying.
This is only a section of the code.
It does reset the enableevents at the end.

 

[rlv01]

There are 3 likely possibilities

1. Even though you think your code is not running, it is actually running.
2. Your workbook code is setting Application.EnableEvents to false (and failing to turn it back on because of a crash or a coding error)
3. One of the other open workbooks is setting Application.EnableEvents to false

If I had to guess, my money would be on one of the other open workbooks. As an aide to chasing this down, pick a cell and add a visual tell-tale to provide positive feedback when events are turned on.

'Color toggle to provide visual feedback that Events are turned on
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim R As Range
    With Range("$M$1").Interior
        If .Color = RGB(255, 255, 0) Then             'Yellow
            .Color = RGB(0, 255, 0)                   'green
        Else
            .Color = RGB(255, 255, 0)                 'yellow
        End If
    End With
End Sub

Obviously, you can pick another cell than M1. But the idea is to go on about your business until you realize that the color has stopped toggling. Then you can start troubleshooting.

 

[Archer5]

Hi,
Thank you for this suggestion. I will try it and get back to you.
Cheers