macro causing printing problem

[ed.ayers315]

Hi Folks,

Happy Mother's Day!!

The following code is causing issues when I try to set up a print range with a macro. This error pops up

Run Time Error '13':

In the code below, the degger stops at the red font

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("g21:h21,g23:h23,g25:h25,e25,g27:h27,g29:h29,g31:h31,g33:h33,g35:h35,g37:h37,g39:h39,g41:h41,g43:h43,g48:h48,g50:h50,g52:h52,g54:h54,g56:h56,g58:h58,g60:h60,g62:h62,g64:h64,g66:h66")) Is Nothing Then
Cancel = True
If VarType(Target.Value) = vbBoolean Then
Target.Value = Not (Target.Value)
Else
Target.Value = IIf(Target.Value = 1, Null, 1)
End If
End If

Dim lZoom As Long
Dim lZoomDV As Long
Dim lDVType As Long
lZoom = 119
lZoomDV = 180
lDVType = 0
Application.EnableEvents = False
On Error Resume Next
lDVType = Target.Validation.Type

On Error GoTo errHandler
If lDVType <> 3 Then
With ActiveWindow
If .Zoom <> lZoom Then
.Zoom = lZoom
End If
End With
Else
With ActiveWindow
If .Zoom <> lZoomDV Then
.Zoom = lZoomDV
End If
End With
End If
exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:
GoTo exitHandler

ActiveSheet.Calculate
ActiveWindow.SmallScroll
Application.WindowState = Application.WindowState
End Sub

I do not what is wrong with the code, any suggestions?

 

[ed.ayers315]

It is changing 1 to 0 and 0 to one but is back to also changing them again when I select the range.

 

[VoG]

My mind-reading abilities are exhausted. What do you actually want to happen?

 

[ed.ayers315]

Thanks VoG,

It is probably just too hard!

In this area, I need to input text in all but the intersecting ranges

g21:h21,g23:h23,g25:h25,g27:h27,g29:h29,g31:h31,g33:h33,g35:h35,g37:h37,g39:h39,g41:h41,g43:h43,g48:h48,g50:h50,g52:h52,g54:h54,g56:h56,g58:h58,g60:h60,g62:h62,g64:h64,g66:h66

I click one of the above and change the status to 1 or 0. Once I actually change it I do not want it to change when selecting the "range" only when the individual cell is selected.

And not change these: the f's have static text and the i's have static text

f21:i21,f23:i23,f25:i25,f27:i27,f29:i29,f31:i31,f33:i33,f35:i35,f37:i37,f39:i39,f41:i41,f43:i43,f48:i48,f50:i50,f52:i52,f54:i54,f56:i56,f58:i58,f60:i60,f62:i62,f64:i64,f66:i66

 

[VoG]

Maybe

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static ChangedAlready As Boolean
Dim c As Range
If Not ChangedAlready Then
    For Each c In Target
        If VarType(c.Value) = vbBoolean Then
            c.Value = Not c.Value
            ChangedAlready = True
        Else
            If IsNumeric(c.Value) And c.Value <> vbNullString Then
                c.Value = IIf(c.Value = 1, 0, 1)
                ChangedAlready = True
            End If
        End If
    Next c
End If
End Sub

 

[ed.ayers315]

Hi VoG,

Thanks for taking the time to walk "Crawl" me through this.

I used your new code and added a double click to insert symbols so it essential took out the selectchange out of the picture for the rest of the areas.

I would not have gotten here without your help!

Thanks! The Avengers-pooh you folks are my "Heros"