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?

 

[VoG]

Try

Target.Value = IIf(Target.Value = 1, vbNullString, 1)

 

[ed.ayers315]

Hi VoG,

Thanks but, it still stops in the same place.

Same error, type mismatch

 

[VoG]

Strange because this works for me

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If VarType(Target.Value) = vbBoolean Then
    Target.Value = Not Target.Value
Else
    Target.Value = IIf(Target.Value = 1, vbNullString, 1)
End If
End Sub

Tested with Boolean, numbers (including dates). However it errors if the Target contains an error value like #N/A. Is that a possibility?

 

[ed.ayers315]

I can not find any.

B27:d27 is DV
E27 is DV
G27 is where on select change changes from "1" to "0"
Row b28:e28 is for text
as well as f28:i28

I have 20 or so of this combination down my report.

The error only comes up if these blocks are part of the selected area. Whether or not they have information or blank.
<!-- ######### Start Created Html Code To Copy ########## -->

Excel Workbook
	B	C	D	E	F	G	H	I
27					Supplier			Customer
28
SERVICE_REPORT

<!-- ######### End Created Html Code To Copy ########## -->

 

[VoG]

If you are selecting multiple cells you need something like

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

 

[ed.ayers315]

Oh pooh,

I pasted this code in under this

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

and it replaced everything with a 1

Where should I paste it?

 

[VoG]

It (and your original) would replace blanks and text with 1. What should happen with blanks etc.

 

[ed.ayers315]

Only in columns g and h where are they are the conditional format is red.

 

[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) Then c.Value = IIf(c.Value = 1, vbNullString, 1)
    End If
Next c