OaklandJim
Well-known Member
- Joined
- Nov 29, 2018
- Messages
- 875
- Office Version
- 365
- Platform
- Windows
An event handler calls sub below. Code at the end causes a Run-time Error 13, Type Mismatch error.
Debug.print results are as follows:
$B$15 within $B$9:$B$49
iSelectedPersonIndex = 7
Typename(iSelectedPersonIndex) = Long
Typename(iCheckboxRow) = Long
So Excel says that the two variables are the same type but I am getting that error message that has me flummoxed. What oversight involving a basic concept am I comitting?
Jim
VBA Code:
Option Explicit
' ----------------------------------------------------------------
' Procedure Name: UserSelectedPerson
' Purpose: Process user request for person number piPerson.
' Procedure Kind: Sub
' Procedure Access: Public
' Parameter piPerson (Long): Person #1 or person #2.
' Parameter prUserCell (Range): The cell that was changed, Target paramerter from event handler.
' Author: Jim
' Date: 2/26/2025
' ----------------------------------------------------------------
Sub UserSelectedContact(piPerson As Long, prUserCell As Range)
' Range containg all checkboxes.
Dim rCheckboxes As Range
' Index of the selected contact in rCheckboxes.
Dim iSelectedContactIndex As Long
' Used to iterate through range rCheckboxes
Dim rCell As Range
' During iterations through rCheckboxes keep count of which index
' within rCheckboxes is being processed.
Dim iCheckboxRow As Long
' Range containing Checkboxes
Set rCheckboxes = [ContactsData].Range("AcceptCheckboxes_Person" & piPerson)
Debug.Print prUserCell.Address & " within " & rCheckboxes.Address
' Determine selected item index within rCheckboxes.
iSelectedContactIndex = prUserCell.Row - rCheckboxes.Cells(1).Row + 1
Debug.Print "iSelectedContactIndex = " & iSelectedContactIndex
' ---------------------------------------
' Clear any Existing Checkboxes
' ---------------------------------------
Debug.Print "Typename(iSelectedContactIndex) = " & TypeName(iSelectedContactIndex)
Debug.Print "Typename(iCheckboxRow) = " & TypeName(iCheckboxRow)
For Each rCell In rCheckboxes
iCheckboxRow = iCheckboxRow + 1
'
'This generates an error message indicating a type mismatch
Debug.Print "iCheckboxRow " & iCheckboxRow & ". iCheckboxRow <> iSelectedContactIndex = " _
& iCheckboxRow <> iSelectedContactIndex
''
'' If iRow <> iSelectedContactIndex Then rCell.Value = ""
'
Next rCell
End Sub
Debug.print results are as follows:
$B$15 within $B$9:$B$49
iSelectedPersonIndex = 7
Typename(iSelectedPersonIndex) = Long
Typename(iCheckboxRow) = Long
So Excel says that the two variables are the same type but I am getting that error message that has me flummoxed. What oversight involving a basic concept am I comitting?
Jim