Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why Type Mismatch error
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why Type Mismatch error
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" wrote in message ... 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. -- 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why Type Mismatch error
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why Type Mismatch error
Then you should make it Case Else not Case "Friday"
-- HTH Bob Phillips "David" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why Type Mismatch error
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").Del ete 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").Del ete 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
Type Mismatch Error | Excel Programming | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming | |||
help with type mismatch error | Excel Programming | |||
Type Mismatch Error Need Help | Excel Programming |