change a cell value and corresponding sheet/tab color based on value on button click

rdoulaghsingh

Board Regular
Joined
Feb 14, 2021
Messages
83
Office Version
  1. 365
Platform
  1. Windows
Hope everyone is having a great weekend! Ok...here we go. I have a button on several sheets in a workbook which is supposed to call one function. The function is supposed to look at a sheet named "CONTENTS" and search column B for matching sheet name. Eg. If the active sheet I'm working on is "Intro" and I click the button on the page, it should search all column B on "CONTENTS" page and find it's matching string "Intro". When it finds the matching string, I need it to move two to the right of the cell with the matching text value and change the cell value 2 rows across to "In Progress" and then change the active sheet tab color with the button to Yellow. I found the code below on the internet which works to change the sheet/tab color of the matching string in the CONTENTS B column, but haven't been able to modify it to a working state to do the above. Any help would be greatly appreciated!

Dim Clr As Long

If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("D3:D100")) Is Nothing Then
If Target.Value <> "" Then
If Evaluate("isref('" & Target.Offset(, -2).Value & "'!A1)") Then
Target.Value = "In Progress": Clr = 65535
Sheets(CStr(Target.Offset(, -2).Value)).Tab.color = Clr
Else
MsgBox "Sheet " & Target.Offset(, -2).Value & "is not a valid status"
End If
End If
End If
End Sub
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,086
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub rdoulaghsingh()
   Dim Fnd As Range
   
   With Sheets("Contents")
      Set Fnd = .Range("A:A").Find(ActiveSheet.Name, , , xlWhole, , , False, , False)
   End With
   If Fnd Is Nothing Then
      MsgBox "Sheet name not found"
      Exit Sub
   End If
   Fnd.Offset(, 2).Value = "In Progress"
   ActiveSheet.Tab.Color = vbYellow
End Sub
 
Solution

rdoulaghsingh

Board Regular
Joined
Feb 14, 2021
Messages
83
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub rdoulaghsingh()
   Dim Fnd As Range
  
   With Sheets("Contents")
      Set Fnd = .Range("A:A").Find(ActiveSheet.Name, , , xlWhole, , , False, , False)
   End With
   If Fnd Is Nothing Then
      MsgBox "Sheet name not found"
      Exit Sub
   End If
   Fnd.Offset(, 2).Value = "In Progress"
   ActiveSheet.Tab.Color = vbYellow
End Sub
That worked! Thank you! I just had to change the Range to Column B. The one thing I didn't realize is that it doesn't manipulate the dropdown list the way I intended it to. I attached a screenshot of what the sheet looks like. I was hoping when it set the text to "In Progress", it would manipulate the dropdown and record the %age change on the chart. I have it set to when the user manually drops down the list and selects one of the statuses, it calculates the percentage of the project completed. The script above does everything except manipulate the dropdown in such a way that it reads it on the chart. Any ideas?
 

Attachments

  • image_2021-02-20_114547.png
    image_2021-02-20_114547.png
    63.4 KB · Views: 6

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,086
Office Version
  1. 365
Platform
  1. Windows
I have it set to when the user manually drops down the list and selects one of the statuses, it calculates the percentage of the project completed.
How?
 

rdoulaghsingh

Board Regular
Joined
Feb 14, 2021
Messages
83
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I have the code I sent before (copied below again) in the "CONTENTS" worksheet sub. If the user selects "Not Started" it changes the tab color for the associated action in the contents red and so on depending on the status. I then have a hidden sheet which is calculating the percentage completed based on the dropdown action and writes it to the chart. It's a simple function to count the number of actions on the CONTENTS page from Col D and then assign a value based on "In progress" or "Completed".

Sub Worksheet_Change(ByVal Target As Range)
Dim clr As Long

If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("D3:D100")) Is Nothing Then
If Target.Value <> "" Then
If Evaluate("isref('" & Target.Offset(, -2).Value & "'!A1)") Then
Select Case Target.Value
Case "Not Started": clr = 255
Case "In Progress": clr = 65535
Case "Completed": clr = 3962880
End Select
Sheets(CStr(Target.Offset(, -2).Value)).Tab.Color = clr
Else
MsgBox "Sheet " & Target.Offset(, -2).Value & "is not a valid status"
End If
End If
End If
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,086
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad it's sorted & thanks for the feedback
 

rdoulaghsingh

Board Regular
Joined
Feb 14, 2021
Messages
83
Office Version
  1. 365
Platform
  1. Windows
Glad it's sorted & thanks for the feedback
Me again. Sorry. Is there any way to have the code work the other way around? Just thinking along the lines if the user manually changes a tab color by right clicking and selecting a color. What I've done in this event is create a refresh button which I hope to read all sheet/tab colors and update the status column cell value depending on sheet name and matching row name. For example, if a user right clicks and manually changes the Introduction 800-171 tab and change the color to vbyellow, then the D3 cell will change to "In Progress" and color fill yellow. Is there any way to accomplish that?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,109
Messages
5,768,148
Members
425,458
Latest member
Jaspal1996

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