Run time error 1004 1

[osx99]

I have the following 2 events which run perfectly well independently in an excel worksheet

However when the first code runs it triggers the second code event and i get an error Run time error '1004' Select method of range class failed

What am i doing wrong here?


This code sets everything in the range to false. The true or false values are generated using checkboxes

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Application.Calculation = xlManual
Application.EnableEvents = False
    Application.Sheets("708 Earthworks").Activate
    Application.Sheets("708 Earthworks").Range("i6:i66").Value = False
    Application.Sheets("708 Earthworks").Range("F4").Select
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

This code hides/unhides data on another worksheet to generate selected data points for chart

Private Sub CheckBox29_Click()
Application.ScreenUpdating = False
Application.Calculation = xlManual
Application.Sheets("708 Workings").Activate
Dim MyCell As Range, MyRange As Range
    Set MyRange = Application.Sheets("708 Workings").Range("o502:o664")
    For Each MyCell In MyRange
        MyCell.Select
        If MyCell.Value <> 0 Then
        Selection.EntireRow.Hidden = True
        
        Else
        Selection.EntireRow.Hidden = False
        End If
    Next MyCell
    
Application.Sheets("708 Earthworks").Activate
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

 

[xlbo]

hmm - not sure why the event is firing given that you have set them to false but maybe something like this:

Private Sub CheckBox29_Click()
Application.ScreenUpdating = False
Application.Calculation = xlManual
Application.Sheets("708 Workings").Activate
Dim MyCell As Range, MyRange As Range
    
[b]if application.enableevents = false then exit sub[/b]

Set MyRange = Application.Sheets("708 Workings").Range("o502:o664")
    For Each MyCell In MyRange
        MyCell.Select
        If MyCell.Value <> 0 Then
        Selection.EntireRow.Hidden = True
        
        Else
        Selection.EntireRow.Hidden = False
        End If
    Next MyCell
    
Application.Sheets("708 Earthworks").Activate
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

"However when the first code runs it triggers the second code event"

HOW? There's NOTHING in your posted code that causes anthing to be triggered, as I can tell.

However, your Row Hiding code is very inefficient. You ought to look into using a Filter, (AutoFilter) to hide rows containing the criteria <>0.

I use AutoFilter many times in conjunction with ComboBox controls on a Chart to change what is plotted.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[osx99]

'if application.enableevents = false then exit sub' - works great thanks!

"However when the first code runs it triggers the second code event" - to be honest I've been having this trouble with weird firing events for a while. I'm starting to wonder if I need a patch or if there is perhaps some vba add ins that may be causing these problems.

I might get our IM people to do a fresh install for me

 

[xlbo]

I think I can see what is causing the event to be called - I would imagine that

.Range("i6:i66").Value = False

is setting the related cell for the checkbox and therefore changing its value which is equivalent to the click event but you have set enableevents to be false already so it shouldn;t (AFAIK) fire

Glad the little fix worked tho

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question