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

--
David

[Kassie]

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.
--
[email protected]ve_2nd_at. Randburg, Gauteng, South Africa


"David" wrote:

> 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
>
> --
> David
>

[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


--
HTH

Bob Phillips

"Kassie" <[email protected]> wrote in message
news:[email protected]...
> 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.
> --
> [email protected]ve_2nd_at. Randburg, Gauteng, South Africa
>
>
> "David" wrote:
>
> > 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
> >
> > --
> > David
> >

[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

<snip>

> 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.

--
David

[Bob Phillips]

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

--
HTH

Bob Phillips

"David" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> <snip>
>
> > 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.
>
> --
> David

[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

--
David