| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Visual Basic Error Run Time Error, Type Mismatch
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual Basic Error Run Time Error, Type Mismatch
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 Meg Partridge wrote: 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 -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual Basic Error Run Time Error, Type Mismatch
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 "Meg Partridge" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual Basic Error Run Time Error, Type Mismatch
It seems to have worked! Thank you so much!!!
"Dave Peterson" wrote: 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 Meg Partridge wrote: 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 -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual Basic Error Run Time Error, Type Mismatch
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) "Jim Thomlinson" wrote in 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 "Meg Partridge" wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual Basic Error Run Time Error, Type Mismatch
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 -- Rick (MVP - Excel) "Meg Partridge" wrote in message ... It seems to have worked! Thank you so much!!! "Dave Peterson" wrote: 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 Meg Partridge wrote: 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 -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual Basic Error Run Time Error, Type Mismatch
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. "Rick Rothstein" wrote: 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) "Jim Thomlinson" wrote in 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 "Meg Partridge" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual Basic Error Run Time Error, Type Mismatch
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) "Meg Partridge" wrote in 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. "Rick Rothstein" wrote: 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) "Jim Thomlinson" wrote in 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 "Meg Partridge" wrote: 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual Basic Error Run Time Error, Type Mismatch
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" wrote: 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) "Meg Partridge" wrote in 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. "Rick Rothstein" wrote: 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) "Jim Thomlinson" wrote in 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 "Meg Partridge" wrote: 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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual Basic Error Run Time Error, Type Mismatch
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" wrote in message ... 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" wrote: 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) "Meg Partridge" wrote in 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. "Rick Rothstein" wrote: 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) "Jim Thomlinson" wrote in 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 "Meg Partridge" wrote: 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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual Basic Error Run Time Error, Type Mismatch
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" wrote: 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" wrote in message ... 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" wrote: 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) "Meg Partridge" wrote in 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. "Rick Rothstein" wrote: 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) "Jim Thomlinson" wrote in 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 "Meg Partridge" wrote: 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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual Basic Error Run Time Error, Type Mismatch
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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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) "Meg Partridge" wrote in 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. "Rick Rothstein" wrote: 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) "Jim Thomlinson" wrote in 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 "Meg Partridge" wrote: 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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual Basic Error Run Time Error, Type Mismatch
Thanks ... it must be something with our software at work ... I am using 2003
too "Rick Rothstein" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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) "Meg Partridge" wrote in 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. "Rick Rothstein" wrote: 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) "Jim Thomlinson" wrote in 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 "Meg Partridge" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| run-time error 13: Type mismatch? | Excel Discussion (Misc queries) | |||
| xpath error? Runtime Error 13 type mismatch | Excel Discussion (Misc queries) | |||
| xpath error? Runtime Error 13 type mismatch | Excel Discussion (Misc queries) | |||
| Visual Basic macro run time error(13) type mismatch | Excel Discussion (Misc queries) | |||
| Why type mismatch - R/T error 13 | Excel Discussion (Misc queries) | |||
Linear Mode
