Runtime error '9' Subscript out of range on passing in sheet's codename

Question

I am running into a subscript out of range error and I don't know what that means. The error specifically occurs at a sub called RedBorder that is called within a sub called validateDropDown, validateDropDown is in a module called validationTools, so is RedBorder. validateDropDown is called in ValidateSheet and ValidateSheet is called from ThisWorkBook in a Workbook_Open sub. I had asked this question before but alot of people asked me to create a minimal, complete and verifiable example. I am fairly new to both stackoverflow and vba. Thanks in advance

like this:

sheet8.ValidateSheet

this is my ValidateSheet

Public Sub ValidateSheet()

   allowEdit

   ValidationTools.validateDropDown Sheet8, cbApplication, CB_APPLICATION_ERR_RANGE
   disallowEdit

End Sub

this is RedBorder:

Public Sub RedBorder(sheet As Worksheet, rangeString As String)

  With Worksheets("sheet").Range(rangeString).Borders
    .LineStyle = xlContinuous
    .Color = vbRed
    .Weight = xlThin
  End With

End Sub

this is validateDropDown:

Public Sub validateDropDown(sheet As Worksheet, ddl As msforms.ComboBox, rangeString As String)
  With sheet
   If ddl.ListIndex = -1 Then
    RedBorder sheet, rangeString
    'add message
    .Range(rangeString).Value = "Please make a selection"

   Else
    NoBorder sheet, rangeString
    'remove message
     .Range(rangeString).Value = ""
  End If
 End With
End Sub

Please go back and delete your old question as this is a better explanation to that one. stackoverflow.com/questions/45741654/… — Scott Craner
Or rather, edit it into shape - the more poorly-received questions you have on your account, the more likely the system blocks you from asking. — Mathieu Guindon

braX braX · Accepted Answer · 2017-08-17T19:24:39

sheet is already a Worksheet object, so you dont put it in quotes or use the Worksheets() part either.

Public Sub RedBorder(sheet As Worksheet, rangeString As String)

  With sheet.Range(rangeString).Borders
    .LineStyle = xlContinuous
    .Color = vbRed
    .Weight = xlThin
  End With

End Sub

Runtime error '9' Subscript out of range on passing in sheet's codename

1 Answers