issues regarding codes not working all of a sudden

[ipbr21054]

Morning,
I noticed this yesterday & happening again this morning.
I have a workbook with say 8 worksheets in it.
On certain sheets there is a code where when i click a cell that active cell is one color & the row is another.
Also when i fist open the worksheet a user form will open.

Neither of the above now work.
I open the worksheet & start clicking cells but none change color.
I open a worksheet but the user form does not automatically open,the button on the same shet opens the form fine if i click it though.

Anything spring to mind as to why this has happened in the last two days.
No code etc has been altered.

Thanks

 

[KennyGreens]

Please paste the code for us to take a look at.

 

[ipbr21054]

There are various codes on each sheet so to much to put here.
Ive just opened the workbook again & now its works but this was the same as yesterday.
100% didnt work then later on back to normal.

 

[gallen]

Something is stopping macros running. Usually permissions. Check to see if there is an "Enable Macros" button in a yellow bar at the top when you open.
If there isn't ensure code is allowed to run by opening the immediate window and entering

Application.EnableEvents = True

If you have other workbooks open with code running, they could prevent this code.

 

[ipbr21054]

Hi,
I have tried but every time i open at present it works fine,thus i dont see any yellow button.

This is the only workbook open.

The code supplied below would possibly be the page with the least on so using this as an example where do i need to put the code you advise ?
On this page i only have where i select a select & that cell is green where the row is blue.
So the code would need to be added at the end of that code ?

Also the cloning user form should also open at start up,so same again add to end of that code ?

If incorrect then please advise thanks.

Private Sub CommandButton1_Click()CloningForm.Show
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Count = 1 And Not .HasFormula Then
            Application.EnableEvents = False
            .Value = UCase(.Value)
            Application.EnableEvents = True
        End If
    End With
End Sub
Private Sub NewRowButton_Click()
Sheets("CLONING").Range("D4").Select
ActiveCell.EntireRow.Insert Shift:=xlDown
Sheets("CLONING").Range("D4:I4").Select
Selection.Borders.Weight = xlThin
Sheets("CLONING").Range("D4").Select
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim myStartCol As String
    Dim myEndCol As String
    Dim myStartRow As Long
    Dim myLastRow As Long
    Dim myRange As Range


    If Target.Cells.Count > 1 Then Exit Sub
    
    Application.ScreenUpdating = False
    
'   *** Specify columns to apply this to ***
    myStartCol = "D"
    myEndCol = "I"


'   *** Specify start row ***
    myStartRow = 3
    
'   Use first column to find the last row
    myLastRow = Cells(Rows.Count, myStartCol).End(xlUp).Row
    
'   Build range to apply this to
    Set myRange = Range(Cells(myStartRow, myStartCol), Cells(myLastRow, myEndCol))
    
'   Clear the color of all the cells in range
    myRange.Interior.ColorIndex = 6
    
'   Check to see if cell selected is outside of range
    If Intersect(Target, myRange) Is Nothing Then Exit Sub
    
'   Highlight the row and column that contain the active cell
    Range(Cells(Target.Row, myStartCol), Cells(Target.Row, myEndCol)).Interior.ColorIndex = 8
Target.Interior.Color = vbGreen
    Application.ScreenUpdating = True


End Sub
Private Sub Worksheet_Activate()
CloningForm.Show
End Sub

 

[gallen]

Sorry, I assumed too much.

Next time it isn't working, from within Excel press Alt + F11 to bring up the code window.

Then from there press Ctrl + G to bring up the immediate window. In the window that appears type this:

?Application.EnableEvents

Note the '?' at the start.

This will then reply with either True or False. If false it means code isn't going to run on any event.

If it replies with True then your issues lie else where but this is the first place to look.

 

[ipbr21054]

Thanks very much i assume it will be tomorrow now

 

[ipbr21054]

Hi,
As per post #6 this morning i have done as advised.
The return answer was False

 

[ipbr21054]

Just to follow up from the above post some minutes ago,
I have saved the sheet & closed it,carried on with my work then took a look at it again.

This time its working ??? strange

 

[gallen]

If it is false then no code will run.

Some code in another worksheet/workbook must be causing this. If you close all excel sheets down then re-open, it will be set to true automatically. If you have sheets still open however, then it will remain as "False"

My guess is that some code that disables events either errors out woithout re-enabling or the code simply doesn't re-enable.

Disabling events is much needed to prevent many issues, however it can cause many issues if never enabled again.

All I can suggest is that when it isn't working, make a note of all Workbooks that are open or have been open. Then search all these for code that states "EnableEvents = False" Your culprit will be in there somewhere