View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.programming
David David is offline
external usenet poster
 
Posts: 195
Default 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