Macro turning on itself

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
I have go this simple macro:
Code:
Sub Unhideall()
    Range("a12:a250").Select
    Selection.EntireRow.Hidden = False
    Range("a1").Select
End Sub
When opening workbook am getting error message and the VBA debug is poiting to this macro.
This macro is called by other macros

Like this one:
Code:
Private Sub ListBox2_Click()
 Application.ScreenUpdating = False
 Dim cell As Range
Dim StartDate As Date
Dim EndDate As Date
   
If Range("F1").Value = "All" Then
Call Unhideall
Else
           
            For Each cell In Range("g12:g250")
            cell.EntireRow.Hidden = True
If cell.Value >= Range("G1") And cell.Value <= Range("H1") Then
cell.EntireRow.Hidden = False
End If
        
    Next
End If
End Sub
Anyone can have idea what can couse the macro to trigger itself?
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Is your listbox linked to a range on the sheet? Do you have any selectionchange event code?

By the way, you can reduce that code to:
Code:
Sub Unhideall()
    Range("a12:a250").EntireRow.Hidden = False
End Sub
 
Upvote 0
Background:
I have got a table of data that is being filter based on button
One of many buton is:
Code:
Sub OnTimeinvbutton()
Call Unhideall
Application.ScreenUpdating = False
Dim cell As Range
For Each cell In Range("K12:K250")
If cell.Value <> "On time" Then
cell.EntireRow.Hidden = True
End If
Next
End Sub
( Macro which unhide all rows then shows only the one which are not equal to "on time")
There is a macro which trigger an array:
Code:
Sub EnterFormualAndDelete()
Application.ScreenUpdating = False
With Range("AI2")
.FormulaArray = "=IFERROR(INDEX(R2C[-12]:R165C[-12],SMALL(" & _
"IF(R2C31:R165C31>0,ROW(R2C31:R165C31)-ROW(R2C31)+1),ROWS(R1C1:R[-1]C[-34]))),"""")"
.AutoFill Destination:=.Resize(159), Type:=xlFillDefault
.Offset(, 1).EntireColumn.Insert
With .Offset(, 1).Resize(159)
.FormulaR1C1 = "=IF(RC[-1]="""","""",IF(COUNTIF(R2C[-1]:RC[-1],RC[-1])=1,1,""""))"
.Value = .Value
On Error Resume Next
.SpecialCells(xlBlanks).Offset(, -1).ClearContents
On Error GoTo 0
.EntireColumn.Delete
End With
End With
Application.ScreenUpdating = True
End Sub
Rorya, there is no selectionchange event
(It used to be but it was removed)

Andrew,
The error I'm getting is:
"Select method of Range class failed"
An this part
Range("A12:A250").Select
is being highlighted in yellow.
There is not Workbook_Open triggered macro.
 
Upvote 0
Did you try the revised version I posted? (it really needs a specific worksheet reference though!)
 
Upvote 0
Thanks Rory
Removing the Screenupdate helped.

For curiosity.
Why this had trigger the macro to run?
 
Upvote 0
I didn't say (or do) anything about screen updating? I just removed the selecting from the code.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top