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 :oops: THIS ONE IS :oops: DRIVING ME :oops: CRAZY!! please help :pray:

Thanks!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

bs0d

Well-known Member
Joined
Dec 29, 2006
Messages
622
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,129,557
Messages
5,637,043
Members
416,955
Latest member
Gohar hussain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top