VBA to activate a sheet using a particular cell entry

knownothingnovice82

New Member
Joined
Apr 8, 2019
Messages
5
Hello all!

I'm working on a spreadsheet questionnaire that requires a ZIP Code entry. Each ZIP Code entry may require additional info based on region. This addition questionnaire will be on the sheet I'd to activate. I'd also like to have that ZIP entry to auto-populate the corresponding city & state in the cell underneath it. I've been pretty successful in finding what i need for other functions, but :banghead: THIS ONE IS :banghead: DRIVING ME :banghead: CRAZY!! please help :pray:

Thanks!
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

bs0d

Well-known Member
Joined
Dec 29, 2006
Messages
620
At a very basic level, you'd be looking at something like this to activate another sheet based on the the value in a specific cell:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Sheets("Sheet1").Range("A1") = 90210 Then
    Sheet2.Select
    
End If
End Sub
 

knownothingnovice82

New Member
Joined
Apr 8, 2019
Messages
5
Thanks bs0d!
the code works like a charm!
how can i alter it to activate the sheet base on an entry from a range listed on another sheet?
i've tried the following:
Private Sub Worksheet_Change(ByVal Target As range)


If Sheets("Manual Form Screening").range("C13:z13") = range("Utilities!a3:a2207") Then
Sheet2.Activate
End If


End Sub

Private Sub Worksheet_Change(ByVal Target As range)


If Sheets("Manual Form Screening").range("C13:z13") = Worksheets("Utilities").range("a3:a2207") Then
Sheet2.Activate
End If


End Sub



Private Sub Worksheet_Change(ByVal Target As range)


If Sheets("Manual Form Screening").range("C13:z13") = Worksheets("sheet3").range("a3:a2207") Then
Sheet2.Activate
End If


End Sub

none have worked
 

Watch MrExcel Video

Forum statistics

Threads
1,095,680
Messages
5,445,931
Members
405,371
Latest member
Theglyde

This Week's Hot Topics

Top