CODEWITHSUNDEEP
excelvba
accurist
accurist

Reputation: 51

Type mismatch error when using ActiveCell.Row

Having some trouble here. On Sheet called "Start" I have column "I" In this column all the fields in the rows have the same dropdown:

I would like to open sheet in the workbook corresponding to the value from the dropdown. So for instance if I choose sheet 1 from the list "Sheet 1" in the workbook will open. As mentioned before these dropdown span across entire column "I" so no matter which row I select to use the dropdown I would like the same thing to happen. I have used Active Cell.Row but getting Type mismatch error:

Private Sub Worksheet_Change(ByVal Target As Range)
        
If Rows(ActiveCell.Row).Value = "Sheet 1" Then
            Worksheets("Sheet 1").Visible = True
            Worksheets("Sheet 1").Activate
            Else
If Rows(ActiveCell.Row).Value = "Sheet 2" Then
            Worksheets("Sheet 2").Visible = True
            Worksheets("Sheet 2").Activate
            Else
If Rows(ActiveCell.Row).Value = "Sheet 3" Then
            Worksheets("Sheet 3").Visible = True
            Worksheets("Sheet 3").Activate
End If
End If
End If

End Sub

Any Ideas ?

Upvotes: 0

Views: 235

Answers (2)

Gary's Student
Gary's Student

Reputation: 96773

You should use Target rather than ActiveCell:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim v1 As Long, v2 As String
    
    v1 = Target.Column
    v2 = Target.Value
    
    If v1 <> 9 Then Exit Sub
    If v2 = "Sheet 1" Or v2 = "Sheet 2" Or v2 = "Sheet 3" Then
            Worksheets(v2).Visible = True
            Worksheets(v2).Activate
    End If
End Sub

Upvotes: 1

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11988

You can do:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 Then ThisWorkbook.Worksheets(Target.Value).Activate
End Sub

Note this will only work if Target.Value match the sheet name exactly

Upvotes: 1

Related Questions