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]

This worked for F and I, not replacing current text with "1"; every thing else selected ended up with a "1"

Here is where I pasted it.

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
Dim c As Range
For Each c In Target
If VarType(c.Value) = vbBoolean Then
c.Value = Not c.Value
Else
If IsNumeric(c.Value) Then c.Value = IIf(c.Value = 1, vbNullString, 1)
End If
Next c
End If

 

[VoG]

Good old VBA - considers blanks to be numeric

Yty

Dim c As Range
For Each c In Target
    If VarType(c.Value) = vbBoolean Then
        c.Value = Not c.Value
    Else
        If IsNumeric(c.Value) And c.Value <> vbNullString Then c.Value = IIf(c.Value = 1, vbNullString, 1)
    End If
Next c

 

[ed.ayers315]

Now were getting somewhere

But, now when I click those intersecting cells that I need the selectchange to change the value to "1" or to "0" with the click are not working.

Should I move that code below the Next C?

 

[VoG]

Try

Dim c As Range
For Each c In Target
    If VarType(c.Value) = vbBoolean Then
        c.Value = Not c.Value
    Else
        If IsNumeric(c.Value) And c.Value <> vbNullString Then c.Value = IIf(c.Value = 1, 0, 1)
    End If
Next c

 

[ed.ayers315]

No change

 

[VoG]

I'm finding it hard to understand what result you expect.

For me that changes 0s to 1s and 1s to 0s.

 

[ed.ayers315]

Sorry for being a pain.

Once I enter data into each of these blocks with text and click those red cells in columns G & H, I need those to stay in that state whether it is 1 or 0, once I have finished my input I want to print the report. Before when I selected the range to print I got the error.

I reset the macro and tried to change the values in G and H, mine did not change value on the click.

 

[VoG]

I suspect that events have become disabled at some point. Press CTRL + G to open the Immediate window and in it type

Application.EnableEvents = True

then press Enter.

Then try again.

 

[ed.ayers315]

Nope, still not working

 

[VoG]

Maybe save your work, close Excel then re-open the file.