VBA

jdlerry

New Member
Joined
Apr 6, 2021
Messages
32
Office Version
  1. 2016
Platform
  1. Windows
Hello everyone, I need your help please. I just need VBA code to get my reports working. What I want to do is whenever these dropdowns are selected, it should automatically go to another sheet.

Completed: CCG Response
Completed: GSS Notification
Completed: IV Notification



Another VBA code that I need help of is whenever a dropdown is selected, it should go to the intended sheet. For example, I have a masterlist in sheet 1 which contains projects and from the dropdowns, I will select the name of the owner per project. Once selected, it should automatically move to the intended sheet. The difference with the first concern is the destination sheet. There will be multiple destination sheets which correspond the owner.

Thank you!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Here's how my file looks like. Column K is where I will change the status to either of the three below and should move to Completed Cases once selected. Any help is appreciated!

Completed: CCG Response
Completed: GSS Notification
Completed: IV Notification
 

Attachments

  • Capture.PNG
    Capture.PNG
    21.2 KB · Views: 6
Upvote 0
Maybe this pasted into the sheet module
It assumes your list is in Col "K"
VBA Code:
Private Sub worksheet_selectionChange(ByVal target As Range)
If Not Intersect(target, Range("K:K")) Is Nothing Then
ans = Evaluate("Mid(" & ActiveCell.Address & ", Find("": ""," & ActiveCell.Address & ", 10) + 2, 99)")
Select Case ans
Case Is = ans
Sheets(ans).Select
Case Is = ans
Sheets(ans).Select
Case Is = ans
Sheets(ans).Select
End Select
End If
End Sub
 
Upvote 0
Just saw your last post....so maybe this way
VBA Code:
Private Sub worksheet_selectionChange(ByVal target As Range)
If Not Intersect(target, Range("K:K")) Is Nothing Then
ans = Evaluate("Left(" & ActiveCell.Address & ",9)")
Sheets(ans).Select
End If
End Sub
 
Upvote 0
Just an update. I found this code and it works but my concerns are:
1. Not automatic. I have to go to the Module and click F5 for the code to run and move it to Completed cases.
2. I don't know how to add more options aside from the one's in the module "Completed: CCG Response". How do I add the other 2: Completed: GSS Notification and Completed: IV Notification?

Thank you!
 

Attachments

  • Capture.PNG
    Capture.PNG
    36.9 KB · Views: 3
Upvote 0
Just saw your last post....so maybe this way
VBA Code:
Private Sub worksheet_selectionChange(ByVal target As Range)
If Not Intersect(target, Range("K:K")) Is Nothing Then
ans = Evaluate("Left(" & ActiveCell.Address & ",9)")
Sheets(ans).Select
End If
End Sub
Thank you! Will check this out but if you can help me please. I posted another concern. I appreciate it
 
Upvote 0
You need to be specific in your request....Your 1st screenshot show 1 tab called completed cases ?
Are you now saying there will be 3 different tabs ?
My first code allows for 3 different Tabs without the Text "Completed", my 2nd code simply goes to a Tab simply called "Completed"?
 
Upvote 0
You need to be specific in your request....Your 1st screenshot show 1 tab called completed cases ?
Are you now saying there will be 3 different tabs ?
My first code allows for 3 different Tabs without the Text "Completed", my 2nd code simply goes to a Tab simply called "Completed"?
Sorry if it was confusing. Yes, I only have 1 tab which is named as Completed cases. My concern is that from the Masterlist tab, under K (status), instead of the word COMPLETED for it to move to Completed Cases, these are the dropdowns.
Completed: CCG Response
Completed: GSS Notification
Completed: IV Notification

So if I select any of the dropdowns, it should move to Completed Cases Tab. Thank you.
 
Upvote 0
This then
VBA Code:
Private Sub worksheet_selectionChange(ByVal target As Range)
If Not Intersect(target, Range("K:K")) Is Nothing Then
ans = Evaluate("Left(" & ActiveCell.Address & ",9)")
Sheets(ans & " Cases").Select
End If
End Sub
 
Upvote 0
This then
VBA Code:
Private Sub worksheet_selectionChange(ByVal target As Range)
If Not Intersect(target, Range("K:K")) Is Nothing Then
ans = Evaluate("Left(" & ActiveCell.Address & ",9)")
Sheets(ans & " Cases").Select
End If
End Sub
[/CODEThan
[QUOTE="Michael M, post: 5670457, member: 52354"]
This then
[CODE=vba]
Private Sub worksheet_selectionChange(ByVal target As Range)
If Not Intersect(target, Range("K:K")) Is Nothing Then
ans = Evaluate("Left(" & ActiveCell.Address & ",9)")
Sheets(ans & " Cases").Select
End If
End Sub

[/QUOTE]
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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
Back
Top