Visual Basic Error Run Time Error, Type Mismatch

M

Meg Partridge

Hello - I am receiving a Run-time error '13' Type mismatch for the code that
I have. After reading the help section of microsoft, I am still lost! The
code I am using is listed below. Any help would be greatly appreciated!!!!
Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("D3") Then
Columns("E:N").Hidden = False
Select Case Target
Case Is = 1
Columns("N").Hidden = True
Case Is = 2
Columns("M:N").Hidden = True
Case Is = 3
Columns("L:N").Hidden = True
Case Is = 4
Columns("K:N").Hidden = True
Case Is = 5
Columns("J:N").Hidden = True
Case Is = 6
Columns("I:N").Hidden = True
Case Is = 7
Columns("H:N").Hidden = True
Case Is = 8
Columns("G:N").Hidden = True
Case Is = 9
Columns("F:N").Hidden = True
Case Is = 10
Columns("E:N").Hidden = True
For Each wSheet In Worksheets
wSheet.Protect Password:="Secret", _
UserInterFaceOnly:=True
Next wSheet
End Select
End If
End Sub
 
D

Dave Peterson

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim wSheet As Worksheet

If Target.Cells.Count > 1 Then
Exit Sub
End If

If Intersect(Target, Me.Range("D3")) Is Nothing Then
Exit Sub
End If

Me.Unprotect Password:="Secret"

Me.Columns("E:N").Hidden = False

Select Case Target
Case Is = 1: Me.Columns("N").Hidden = True
Case Is = 2: Me.Columns("M:N").Hidden = True
Case Is = 3: Me.Columns("L:N").Hidden = True
Case Is = 4: Me.Columns("K:N").Hidden = True
Case Is = 5: Me.Columns("J:N").Hidden = True
Case Is = 6: Me.Columns("I:N").Hidden = True
Case Is = 7: Me.Columns("H:N").Hidden = True
Case Is = 8: Me.Columns("G:N").Hidden = True
Case Is = 9: Me.Columns("F:N").Hidden = True
Case Is = 10: Me.Columns("E:N").Hidden = True
End Select

For Each wSheet In Worksheets
wSheet.Protect Password:="Secret", _
UserInterFaceOnly:=True
Next wSheet

End Sub
 
J

Jim Thomlinson

You have a couple of potential problems...
This line
If Target = Range("D3") Then
is the same as
If Target.Value = Range("D3").Value Then
You probably wanted
If Target.address "$D$3" Then

Your lastcase is the only one that does a protection and even though you
have userinterface set to true there will still be some things that macros
will not be able to do.

There is nothing in there that will necessarily generate a type mismatch...
 
M

Meg Partridge

It seems to have worked! Thank you so much!!!

Dave Peterson said:
Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim wSheet As Worksheet

If Target.Cells.Count > 1 Then
Exit Sub
End If

If Intersect(Target, Me.Range("D3")) Is Nothing Then
Exit Sub
End If

Me.Unprotect Password:="Secret"

Me.Columns("E:N").Hidden = False

Select Case Target
Case Is = 1: Me.Columns("N").Hidden = True
Case Is = 2: Me.Columns("M:N").Hidden = True
Case Is = 3: Me.Columns("L:N").Hidden = True
Case Is = 4: Me.Columns("K:N").Hidden = True
Case Is = 5: Me.Columns("J:N").Hidden = True
Case Is = 6: Me.Columns("I:N").Hidden = True
Case Is = 7: Me.Columns("H:N").Hidden = True
Case Is = 8: Me.Columns("G:N").Hidden = True
Case Is = 9: Me.Columns("F:N").Hidden = True
Case Is = 10: Me.Columns("E:N").Hidden = True
End Select

For Each wSheet In Worksheets
wSheet.Protect Password:="Secret", _
UserInterFaceOnly:=True
Next wSheet

End Sub
 
R

Rick Rothstein

You missed the equal sign in your Target.Address test. Also, since only the
active sheet is being unprotected at the beginning of the routine, then I
would think that should be the only one being re-protected again inside this
routine. In addition to that, I noticed a mathematical relationship in the
Columns ranges versus the content of D3 that allows the entire Select Case
block to be replaced with a single line of code. Here is the routine after
the above are incorporated into it...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wSheet As Worksheet
If Target.Cells.Count = 1 And Target.Address = "$D$3" Then
Me.Unprotect Password:="Secret"
Me.Columns("E:N").Hidden = False
Me.Columns(Chr(79 - Target.Value) & ":N").Hidden = True
Me.Protect Password:="Secret", UserInterFaceOnly:=True
End If
End Sub

This should do what the OP's original code was trying to do.
 
R

Rick Rothstein

I'm confused as to why you are unprotecting the active sheet, but then
re-protecting all the sheets in the workbook at the end of the routine.
Wouldn't you only need to re-protect the single sheet you unprotected at the
beginning of the routine? In the code below, I used a slightly different
Target validation routine than Dave did, and only re-protected the one sheet
you unprotected rather than all the sheets in the workbook, plus I implement
a mathematical equivalent to your entire Select Case block to produce this
routine (which should do what I think your original code was intended to
do)...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wSheet As Worksheet
If Target.Cells.Count = 1 And Target.Address = "$D$3" Then
Me.Unprotect Password:="Secret"
Me.Columns("E:N").Hidden = False
Me.Columns(Chr(79 - Target.Value) & ":N").Hidden = True
Me.Protect Password:="Secret", UserInterFaceOnly:=True
End If
End Sub
 
M

Meg Partridge

I tried this & now I get a different error ... Run-time error '1004',
Application-defined or object-defined error.

The reason I had to add that specific password protection is so columns will
be hidden based the choice in the drop down box.

The only thing that should be unprotected are specific columns depending on
a user's choice with a drop down box. There is a limited number of cells
that a user can input into.
 
R

Rick Rothstein

I do not get any errors when I run the code, so I'm not sure what to tell
you about that... the posted code runs fine for me.. not errors. I did
notice that I forgot to place a limit on the entry in D3 that the code
reacts to (unlike the Select Case block, the calculation replacing the it
will happily calculate a value for any number entered in D3); the code below
places the appropriate range trapping in the code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wSheet As Worksheet
If Target.Cells.Count = 1 And Target.Address = "$D$3" Then
If Range("D3").Value >= 1 And Range("D3").Value <= 10 Then
Me.Unprotect Password:="Secret"
Me.Columns("E:N").Hidden = False
Me.Columns(Chr(79 - Target.Value) & ":N").Hidden = True
Me.Protect Password:="Secret", UserInterFaceOnly:=True
End If
End If
End Sub

With respect to the Protect/Unprotect issue I raised... I was referring to
this part from your original code...

For Each wSheet In Worksheets
wSheet.Protect Password:="Secret", UserInterFaceOnly:=True
Next wSheet

This code snippet runs through *each* sheet in the workbook and turns on the
protection. My point was that you did not have to iterate through *every*
sheet in the workbook because the running code only unprotected the active
sheet... the other sheets remained protected, so there is no need to loop
through them turning on the protection that is already on.
 
M

Meg Partridge

I think you are right ... I forgot I have another macro that I created ... I
get the error Run-time error '1004', Application-defined or object-defined
error and when I click on the debugger it goes to the macro below. It is a
simple recorded macro so I am not sure why I get this error.


Sub Macro1()
Range("D3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[3]C[1]:R[3]C[10])"
Range("D4").Select
End Sub
 
R

Rick Rothstein

My guess would be that you have the macro installed in the code window of
one of your worksheets, but that you have a different worksheet active when
you try to run it. Either quality all your Range statements with the
worksheet they should apply to, for one example...

Worksheets("Sheet2").Range("D4").Select

or move the macro to a Module (Insert/Module from the VB editor's menu bar)
where it will be available for use on any worksheet. As an aside, it is
almost never required to select a range in order to do something with it.
These two statements...

Range("D3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[3]C[1]:R[3]C[10])"

can be replaced by this one...

Range("D3").FormulaR1C1 = "=SUM(R[3]C[1]:R[3]C[10])"

--
Rick (MVP - Excel)


Meg Partridge said:
I think you are right ... I forgot I have another macro that I created ...
I
get the error Run-time error '1004', Application-defined or object-defined
error and when I click on the debugger it goes to the macro below. It is
a
simple recorded macro so I am not sure why I get this error.


Sub Macro1()
Range("D3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[3]C[1]:R[3]C[10])"
Range("D4").Select
End Sub


Rick Rothstein said:
I do not get any errors when I run the code, so I'm not sure what to tell
you about that... the posted code runs fine for me.. not errors. I did
notice that I forgot to place a limit on the entry in D3 that the code
reacts to (unlike the Select Case block, the calculation replacing the it
will happily calculate a value for any number entered in D3); the code
below
places the appropriate range trapping in the code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wSheet As Worksheet
If Target.Cells.Count = 1 And Target.Address = "$D$3" Then
If Range("D3").Value >= 1 And Range("D3").Value <= 10 Then
Me.Unprotect Password:="Secret"
Me.Columns("E:N").Hidden = False
Me.Columns(Chr(79 - Target.Value) & ":N").Hidden = True
Me.Protect Password:="Secret", UserInterFaceOnly:=True
End If
End If
End Sub

With respect to the Protect/Unprotect issue I raised... I was referring
to
this part from your original code...

For Each wSheet In Worksheets
wSheet.Protect Password:="Secret", UserInterFaceOnly:=True
Next wSheet

This code snippet runs through *each* sheet in the workbook and turns on
the
protection. My point was that you did not have to iterate through *every*
sheet in the workbook because the running code only unprotected the
active
sheet... the other sheets remained protected, so there is no need to loop
through them turning on the protection that is already on.
 
M

Meg Partridge

I have the macro in a separate module already ... I guess I will have to
figure something else out so the error does not pop up. Thanks your help
though

Rick Rothstein said:
My guess would be that you have the macro installed in the code window of
one of your worksheets, but that you have a different worksheet active when
you try to run it. Either quality all your Range statements with the
worksheet they should apply to, for one example...

Worksheets("Sheet2").Range("D4").Select

or move the macro to a Module (Insert/Module from the VB editor's menu bar)
where it will be available for use on any worksheet. As an aside, it is
almost never required to select a range in order to do something with it.
These two statements...

Range("D3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[3]C[1]:R[3]C[10])"

can be replaced by this one...

Range("D3").FormulaR1C1 = "=SUM(R[3]C[1]:R[3]C[10])"

--
Rick (MVP - Excel)


Meg Partridge said:
I think you are right ... I forgot I have another macro that I created ...
I
get the error Run-time error '1004', Application-defined or object-defined
error and when I click on the debugger it goes to the macro below. It is
a
simple recorded macro so I am not sure why I get this error.


Sub Macro1()
Range("D3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[3]C[1]:R[3]C[10])"
Range("D4").Select
End Sub


Rick Rothstein said:
I do not get any errors when I run the code, so I'm not sure what to tell
you about that... the posted code runs fine for me.. not errors. I did
notice that I forgot to place a limit on the entry in D3 that the code
reacts to (unlike the Select Case block, the calculation replacing the it
will happily calculate a value for any number entered in D3); the code
below
places the appropriate range trapping in the code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wSheet As Worksheet
If Target.Cells.Count = 1 And Target.Address = "$D$3" Then
If Range("D3").Value >= 1 And Range("D3").Value <= 10 Then
Me.Unprotect Password:="Secret"
Me.Columns("E:N").Hidden = False
Me.Columns(Chr(79 - Target.Value) & ":N").Hidden = True
Me.Protect Password:="Secret", UserInterFaceOnly:=True
End If
End If
End Sub

With respect to the Protect/Unprotect issue I raised... I was referring
to
this part from your original code...

For Each wSheet In Worksheets
wSheet.Protect Password:="Secret", UserInterFaceOnly:=True
Next wSheet

This code snippet runs through *each* sheet in the workbook and turns on
the
protection. My point was that you did not have to iterate through *every*
sheet in the workbook because the running code only unprotected the
active
sheet... the other sheets remained protected, so there is no need to loop
through them turning on the protection that is already on.

--
Rick (MVP - Excel)


I tried this & now I get a different error ... Run-time error '1004',
Application-defined or object-defined error.

The reason I had to add that specific password protection is so columns
will
be hidden based the choice in the drop down box.

The only thing that should be unprotected are specific columns
depending
on
a user's choice with a drop down box. There is a limited number of
cells
that a user can input into.

:

You missed the equal sign in your Target.Address test. Also, since
only
the
active sheet is being unprotected at the beginning of the routine,
then I
would think that should be the only one being re-protected again
inside
this
routine. In addition to that, I noticed a mathematical relationship in
the
Columns ranges versus the content of D3 that allows the entire Select
Case
block to be replaced with a single line of code. Here is the routine
after
the above are incorporated into it...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wSheet As Worksheet
If Target.Cells.Count = 1 And Target.Address = "$D$3" Then
Me.Unprotect Password:="Secret"
Me.Columns("E:N").Hidden = False
Me.Columns(Chr(79 - Target.Value) & ":N").Hidden = True
Me.Protect Password:="Secret", UserInterFaceOnly:=True
End If
End Sub

This should do what the OP's original code was trying to do.

--
Rick (MVP - Excel)


message
You have a couple of potential problems...
This line
If Target = Range("D3") Then
is the same as
If Target.Value = Range("D3").Value Then
You probably wanted
If Target.address "$D$3" Then

Your lastcase is the only one that does a protection and even though
you
have userinterface set to true there will still be some things that
macros
will not be able to do.

There is nothing in there that will necessarily generate a type
mismatch...
--
HTH...

Jim Thomlinson


:

Hello - I am receiving a Run-time error '13' Type mismatch for the
code
that
I have. After reading the help section of microsoft, I am still
lost!
The
code I am using is listed below. Any help would be greatly
appreciated!!!!
Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("D3") Then
Columns("E:N").Hidden = False
Select Case Target
Case Is = 1
Columns("N").Hidden = True
Case Is = 2
Columns("M:N").Hidden = True
Case Is = 3
Columns("L:N").Hidden = True
Case Is = 4
Columns("K:N").Hidden = True
Case Is = 5
Columns("J:N").Hidden = True
Case Is = 6
Columns("I:N").Hidden = True
Case Is = 7
Columns("H:N").Hidden = True
Case Is = 8
Columns("G:N").Hidden = True
Case Is = 9
Columns("F:N").Hidden = True
Case Is = 10
Columns("E:N").Hidden = True
For Each wSheet In Worksheets
wSheet.Protect Password:="Secret", _
UserInterFaceOnly:=True
Next wSheet
End Select
End If
End Sub
 
R

Rick Rothstein

Just so you know... your macro, as originally written, placed in a Module on
my XL2003 works perfectly with no errors.

--
Rick (MVP - Excel)


Meg Partridge said:
I have the macro in a separate module already ... I guess I will have to
figure something else out so the error does not pop up. Thanks your help
though

Rick Rothstein said:
My guess would be that you have the macro installed in the code window of
one of your worksheets, but that you have a different worksheet active
when
you try to run it. Either quality all your Range statements with the
worksheet they should apply to, for one example...

Worksheets("Sheet2").Range("D4").Select

or move the macro to a Module (Insert/Module from the VB editor's menu
bar)
where it will be available for use on any worksheet. As an aside, it is
almost never required to select a range in order to do something with it.
These two statements...

Range("D3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[3]C[1]:R[3]C[10])"

can be replaced by this one...

Range("D3").FormulaR1C1 = "=SUM(R[3]C[1]:R[3]C[10])"

--
Rick (MVP - Excel)


Meg Partridge said:
I think you are right ... I forgot I have another macro that I created
...
I
get the error Run-time error '1004', Application-defined or
object-defined
error and when I click on the debugger it goes to the macro below. It
is
a
simple recorded macro so I am not sure why I get this error.


Sub Macro1()
Range("D3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[3]C[1]:R[3]C[10])"
Range("D4").Select
End Sub


:

I do not get any errors when I run the code, so I'm not sure what to
tell
you about that... the posted code runs fine for me.. not errors. I did
notice that I forgot to place a limit on the entry in D3 that the code
reacts to (unlike the Select Case block, the calculation replacing the
it
will happily calculate a value for any number entered in D3); the code
below
places the appropriate range trapping in the code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wSheet As Worksheet
If Target.Cells.Count = 1 And Target.Address = "$D$3" Then
If Range("D3").Value >= 1 And Range("D3").Value <= 10 Then
Me.Unprotect Password:="Secret"
Me.Columns("E:N").Hidden = False
Me.Columns(Chr(79 - Target.Value) & ":N").Hidden = True
Me.Protect Password:="Secret", UserInterFaceOnly:=True
End If
End If
End Sub

With respect to the Protect/Unprotect issue I raised... I was
referring
to
this part from your original code...

For Each wSheet In Worksheets
wSheet.Protect Password:="Secret", UserInterFaceOnly:=True
Next wSheet

This code snippet runs through *each* sheet in the workbook and turns
on
the
protection. My point was that you did not have to iterate through
*every*
sheet in the workbook because the running code only unprotected the
active
sheet... the other sheets remained protected, so there is no need to
loop
through them turning on the protection that is already on.

--
Rick (MVP - Excel)


message
I tried this & now I get a different error ... Run-time error '1004',
Application-defined or object-defined error.

The reason I had to add that specific password protection is so
columns
will
be hidden based the choice in the drop down box.

The only thing that should be unprotected are specific columns
depending
on
a user's choice with a drop down box. There is a limited number of
cells
that a user can input into.

:

You missed the equal sign in your Target.Address test. Also, since
only
the
active sheet is being unprotected at the beginning of the routine,
then I
would think that should be the only one being re-protected again
inside
this
routine. In addition to that, I noticed a mathematical relationship
in
the
Columns ranges versus the content of D3 that allows the entire
Select
Case
block to be replaced with a single line of code. Here is the
routine
after
the above are incorporated into it...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wSheet As Worksheet
If Target.Cells.Count = 1 And Target.Address = "$D$3" Then
Me.Unprotect Password:="Secret"
Me.Columns("E:N").Hidden = False
Me.Columns(Chr(79 - Target.Value) & ":N").Hidden = True
Me.Protect Password:="Secret", UserInterFaceOnly:=True
End If
End Sub

This should do what the OP's original code was trying to do.

--
Rick (MVP - Excel)


message
You have a couple of potential problems...
This line
If Target = Range("D3") Then
is the same as
If Target.Value = Range("D3").Value Then
You probably wanted
If Target.address "$D$3" Then

Your lastcase is the only one that does a protection and even
though
you
have userinterface set to true there will still be some things
that
macros
will not be able to do.

There is nothing in there that will necessarily generate a type
mismatch...
--
HTH...

Jim Thomlinson


:

Hello - I am receiving a Run-time error '13' Type mismatch for
the
code
that
I have. After reading the help section of microsoft, I am still
lost!
The
code I am using is listed below. Any help would be greatly
appreciated!!!!
Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("D3") Then
Columns("E:N").Hidden = False
Select Case Target
Case Is = 1
Columns("N").Hidden = True
Case Is = 2
Columns("M:N").Hidden = True
Case Is = 3
Columns("L:N").Hidden = True
Case Is = 4
Columns("K:N").Hidden = True
Case Is = 5
Columns("J:N").Hidden = True
Case Is = 6
Columns("I:N").Hidden = True
Case Is = 7
Columns("H:N").Hidden = True
Case Is = 8
Columns("G:N").Hidden = True
Case Is = 9
Columns("F:N").Hidden = True
Case Is = 10
Columns("E:N").Hidden = True
For Each wSheet In Worksheets
wSheet.Protect Password:="Secret", _
UserInterFaceOnly:=True
Next wSheet
End Select
End If
End Sub
 
M

Meg Partridge

Thanks ... it must be something with our software at work ... I am using 2003
too

Rick Rothstein said:
Just so you know... your macro, as originally written, placed in a Module on
my XL2003 works perfectly with no errors.

--
Rick (MVP - Excel)


Meg Partridge said:
I have the macro in a separate module already ... I guess I will have to
figure something else out so the error does not pop up. Thanks your help
though

Rick Rothstein said:
My guess would be that you have the macro installed in the code window of
one of your worksheets, but that you have a different worksheet active
when
you try to run it. Either quality all your Range statements with the
worksheet they should apply to, for one example...

Worksheets("Sheet2").Range("D4").Select

or move the macro to a Module (Insert/Module from the VB editor's menu
bar)
where it will be available for use on any worksheet. As an aside, it is
almost never required to select a range in order to do something with it.
These two statements...

Range("D3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[3]C[1]:R[3]C[10])"

can be replaced by this one...

Range("D3").FormulaR1C1 = "=SUM(R[3]C[1]:R[3]C[10])"

--
Rick (MVP - Excel)


I think you are right ... I forgot I have another macro that I created
...
I
get the error Run-time error '1004', Application-defined or
object-defined
error and when I click on the debugger it goes to the macro below. It
is
a
simple recorded macro so I am not sure why I get this error.


Sub Macro1()
Range("D3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[3]C[1]:R[3]C[10])"
Range("D4").Select
End Sub


:

I do not get any errors when I run the code, so I'm not sure what to
tell
you about that... the posted code runs fine for me.. not errors. I did
notice that I forgot to place a limit on the entry in D3 that the code
reacts to (unlike the Select Case block, the calculation replacing the
it
will happily calculate a value for any number entered in D3); the code
below
places the appropriate range trapping in the code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wSheet As Worksheet
If Target.Cells.Count = 1 And Target.Address = "$D$3" Then
If Range("D3").Value >= 1 And Range("D3").Value <= 10 Then
Me.Unprotect Password:="Secret"
Me.Columns("E:N").Hidden = False
Me.Columns(Chr(79 - Target.Value) & ":N").Hidden = True
Me.Protect Password:="Secret", UserInterFaceOnly:=True
End If
End If
End Sub

With respect to the Protect/Unprotect issue I raised... I was
referring
to
this part from your original code...

For Each wSheet In Worksheets
wSheet.Protect Password:="Secret", UserInterFaceOnly:=True
Next wSheet

This code snippet runs through *each* sheet in the workbook and turns
on
the
protection. My point was that you did not have to iterate through
*every*
sheet in the workbook because the running code only unprotected the
active
sheet... the other sheets remained protected, so there is no need to
loop
through them turning on the protection that is already on.

--
Rick (MVP - Excel)


message
I tried this & now I get a different error ... Run-time error '1004',
Application-defined or object-defined error.

The reason I had to add that specific password protection is so
columns
will
be hidden based the choice in the drop down box.

The only thing that should be unprotected are specific columns
depending
on
a user's choice with a drop down box. There is a limited number of
cells
that a user can input into.

:

You missed the equal sign in your Target.Address test. Also, since
only
the
active sheet is being unprotected at the beginning of the routine,
then I
would think that should be the only one being re-protected again
inside
this
routine. In addition to that, I noticed a mathematical relationship
in
the
Columns ranges versus the content of D3 that allows the entire
Select
Case
block to be replaced with a single line of code. Here is the
routine
after
the above are incorporated into it...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wSheet As Worksheet
If Target.Cells.Count = 1 And Target.Address = "$D$3" Then
Me.Unprotect Password:="Secret"
Me.Columns("E:N").Hidden = False
Me.Columns(Chr(79 - Target.Value) & ":N").Hidden = True
Me.Protect Password:="Secret", UserInterFaceOnly:=True
End If
End Sub

This should do what the OP's original code was trying to do.

--
Rick (MVP - Excel)


message
You have a couple of potential problems...
This line
If Target = Range("D3") Then
is the same as
If Target.Value = Range("D3").Value Then
You probably wanted
If Target.address "$D$3" Then

Your lastcase is the only one that does a protection and even
though
you
have userinterface set to true there will still be some things
that
macros
will not be able to do.

There is nothing in there that will necessarily generate a type
mismatch...
--
HTH...

Jim Thomlinson


:

Hello - I am receiving a Run-time error '13' Type mismatch for
the
code
that
I have. After reading the help section of microsoft, I am still
lost!
The
code I am using is listed below. Any help would be greatly
appreciated!!!!
Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("D3") Then
Columns("E:N").Hidden = False
Select Case Target
Case Is = 1
Columns("N").Hidden = True
Case Is = 2
Columns("M:N").Hidden = True
Case Is = 3
Columns("L:N").Hidden = True
Case Is = 4
Columns("K:N").Hidden = True
Case Is = 5
Columns("J:N").Hidden = True
Case Is = 6
Columns("I:N").Hidden = True
Case Is = 7
Columns("H:N").Hidden = True
Case Is = 8
Columns("G:N").Hidden = True
Case Is = 9
Columns("F:N").Hidden = True
Case Is = 10
Columns("E:N").Hidden = True
For Each wSheet In Worksheets
wSheet.Protect Password:="Secret", _
UserInterFaceOnly:=True
Next wSheet
End Select
End If
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top