VBA

jdlerry

New Member
Joined
Apr 6, 2021
Messages
15
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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

jdlerry

New Member
Joined
Apr 6, 2021
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
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: 4

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,130
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,130
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
 

jdlerry

New Member
Joined
Apr 6, 2021
Messages
15
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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: 1

jdlerry

New Member
Joined
Apr 6, 2021
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
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
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,130
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

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"?
 

jdlerry

New Member
Joined
Apr 6, 2021
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,130
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
 

jdlerry

New Member
Joined
Apr 6, 2021
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
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]
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,989
Members
416,953
Latest member
broexc

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