Why Type Mismatch error

[David]

The following prints out sheets as desired based on menu choice, but
without On Error line bombs immediately with 'Type Mismatch' at indicated
line:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
On Error GoTo Quit '<-- Without this it bombs where indicated below
Dim MonArr, TueArr, WedArr, ThuArr, v, i As Long
MonArr = Array("Intermediate Computer", "Wellness", "Supported
Employment", "Understanding Your Medications", "Creative Writing",
"Picking Up The Pieces")
TueArr = Array("LIFTT", "Wellness", "WRAP", "Sign Language", "Beginning
Computer", "Anger Management")
WedArr = Array("Intermediate Computer", "Wellness", "Supported
Employment", "Understanding Your Symptoms", "WRAP", "Anger Management")
ThuArr = Array("Picking Up The Pieces", "Wellness", "LIFTT", "Adult Basic
Education", "Beginning Computer", "Creative Writing")
Select Case Target.Value
Case "Monday"
v = MonArr
Case "Tuesday"
v = TueArr
Case "Wednesday"
v = WedArr
Case "Thursday"
v = ThuArr
Case "Friday"
Range("A1") = "Wellness"
GoTo Units
End Select
For i = LBound(v) To UBound(v) '<-- Type Mismatch points here
Range("A1") = (v(i))
Select Case v(i)
Case "Beginning Computer", "Intermediate Computer", "Adult Basic
Education", "Creative Writing", "Sign Language"
Range("A14:A20").EntireRow.Hidden = True
Range("E11").Value = 4
Case Else
ActiveSheet.Rows.Hidden = False
Range("E11").Value = 11
End Select
ActiveSheet.UsedRange
ActiveSheet.PrintOut
Next i
Units:
Sheets(2).Visible = True
With Sheets(2)
..Range("A1") = "Maintenance Signups": .PrintOut
..Range("A1") = "Food Service Signups": .PrintOut
End With
Sheets(2).Visible = False
Quit:
End Sub

How to prevent it so I can remove On Error

 

[Guest]

Hi David

I did not test, but I notice you declare your variables as Long, in pther
words to hold numbers, while you in fact want them to be String

Dim MonArr, TueArr, WedArr, ThuArr, v, i As Long
MonArr = Array("Intermediate Computer", "Wellness......... etc.

 

[Bob Phillips]

I don't think Kassie is correct, upper and lower bounds of an array are n
umbers, so long is okay.

I think it is because you don't handle teh case else condition correctly.
You seem to wan t to go to Units if it is not one of the nominated values,
but you just have Goto, not Case Else: Goto.

I also added some code to stop re-entry

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MonArr, TueArr, WedArr, ThuArr, v
Dim i As Long
'>>>>>>>>>>>>> line below added
Application.EnableEvents = False
If Target.Address <> "$A$1" Then Exit Sub
On Error GoTo Quit '<-- Without this it bombs where indicated below
MonArr = Array("Intermediate Computer", "Wellness", _
"Supported Employment", _
"Understanding Your Medications", _
"Creative Writing", "Picking Up The Pieces")
TueArr = Array("LIFTT", "Wellness", "WRAP", "Sign Language", _
"Beginning Computer", "Anger Management")
WedArr = Array("Intermediate Computer", "Wellness", _
"Supported Employment", _
"Understanding Your Symptoms", "WRAP", _
"Anger Management")
ThuArr = Array("Picking Up The Pieces", "Wellness", "LIFTT", _
"Adult Basic Education", "Beginning Computer", _
"Creative Writing")
Select Case Target.Value
Case "Monday": v = MonArr
Case "Tuesday": v = TueArr
Case "Wednesday": v = WedArr
Case "Thursday": v = ThuArr
Case "Friday": Range("A1") = "Wellness"
'>>>>>>>>>>>>> line below changed
Case Else: GoTo Units
End Select
For i = LBound(v) To UBound(v) '<-- Type Mismatch points here
Range("A1") = (v(i))
Select Case v(i)
Case "Beginning Computer", "Intermediate Computer", _
"Adult Basic ", "Education", "Creative Writing", "Sign
Language"
Range("A14:A20").EntireRow.Hidden = True
Range("E11").Value = 4
Case Else
ActiveSheet.Rows.Hidden = False
Range("E11").Value = 11
End Select
ActiveSheet.UsedRange
ActiveSheet.PrintOut
Next i
Units:
Sheets(2).Visible = True
With Sheets(2)
.Range("A1") = "Maintenance Signups": .PrintOut
.Range("A1") = "Food Service Signups": .PrintOut
End With
Sheets(2).Visible = False
Quit:
'>>>>>>>>>>>>> line below added
Application.EnableEvents = True
End Sub

 

[David]

Bob Phillips wrote

I think it is because you don't handle the case else condition correctly.
You seem to want to go to Units if it is not one of the nominated
values, but you just have Goto, not Case Else: Goto

Case "Friday": Range("A1") = "Wellness"
'>>>>>>>>>>>>> line below changed
Case Else: GoTo Units

Actually, the Case "Friday" IS the Case Else, in effect, as it is handled
differently than Monday-Thursday. I just preferred the word "Friday"
staying put because that's what I would have clicked in my custom menu to
skip all the class signup sheets and go straight to Sheets(2). The only
reason I added the Range("A1")= "Wellness" (arbitrary choice) is so that
"Friday" wouldn't have been there when the routine finished.

I was able to take your lead, though, and leave Case "Friday" as is by
adding the Application.EnableEvents = xxx lines. Now everything works.

Thanks.

 

[Bob Phillips]

Then you should make it Case Else not Case "Friday"

 

[David]

Bob Phillips wrote

Then you should make it Case Else not Case "Friday"

I know, I know...

Perhaps if I post all modules/events, it will be clear

ThisWorkbook:
Private Sub Workbook_Activate()
On Error Resume Next
Application.CommandBars(1).Controls("Signups").Delete
Dim vDay, vDays
vDays = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday")
With Application.CommandBars("Worksheet Menu Bar")
With .Controls.Add(msoControlPopup)
..Caption = "Signups"
..BeginGroup = True
For Each vDay In vDays
With .Controls.Add(msoControlButton)
..Caption = vDay
..OnAction = "PrintToday"
End With
Next
End With
End With
End Sub

Private Sub Workbook_Deactivate()
On Error Resume Next
Application.CommandBars(1).Controls("Signups").Delete
End Sub

Module 1:
Private Sub PrintToday()
With Application.CommandBars.ActionControl
Range("A1") = .Caption '<-- This is what puts the Friday in A1
'if I select it from the menu
End With
End Sub

Worksheet:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address <> "$A$1" Then Exit Sub
Dim MonArr, TueArr, WedArr, ThuArr, v, i As Long
MonArr = Array("Intermediate Computer", "Wellness", "Supported
Employment", "Understanding Your Medications", "Creative Writing",
"Picking Up The Pieces")
TueArr = Array("LIFTT", "Wellness", "WRAP", "Sign Language", "Beginning
Computer", "Anger Management")
WedArr = Array("Intermediate Computer", "Wellness", "Supported
Employment", "Understanding Your Symptoms", "WRAP", "Anger Management")
ThuArr = Array("Picking Up The Pieces", "Wellness", "LIFTT", "Adult Basic
Education", "Beginning Computer", "Creative Writing")
Select Case Target.Value
Case "Monday"
v = MonArr
Case "Tuesday"
v = TueArr
Case "Wednesday"
v = WedArr
Case "Thursday"
v = ThuArr
Case "Friday" '<-- doesn't matter if it's Case Else
Range("A1") = "Wellness" '<-- If I leave this out, Friday stays
GoTo Units
End Select
For i = LBound(v) To UBound(v)
Range("A1") = (v(i))
Select Case v(i)
Case "Beginning Computer", "Intermediate Computer", "Adult Basic
Education", "Creative Writing", "Sign Language"
Range("A14:A20").EntireRow.Hidden = True
Range("E11").Value = 4
Case Else
ActiveSheet.Rows.Hidden = False
Range("E11").Value = 11
End Select
ActiveSheet.UsedRange
ActiveSheet.PrintOut
Next i
Units:
Sheets(2).Visible = True
With Sheets(2)
..Range("A1") = "Maintenance Signups": .PrintOut
..Range("A1") = "Food Service Signups": .PrintOut
End With
Sheets(2).Visible = False
Application.EnableEvents = True
End Sub