+ Reply to Thread
Results 1 to 7 of 7

VBA code to run when linked cell value changes

  1. #1
    Registered User
    Join Date
    02-26-2024
    Location
    Houston, TX
    MS-Off Ver
    365
    Posts
    4

    VBA code to run when linked cell value changes

    I have created several worksheets with VBA code that hide columns that are not referenced in a drop down list of months. The sheets work as intended when the drop down list changed manually in each worksheet. Using VBA in the workbook module, I have synchronized the dropdowns in each sheet to mimic each other and I would like the macros in each sheet to run when any one dropdown selection has been changed. My code is below:

    Private Sub Worksheet_Change(ByVal target As Range)

    If target.Address = "$B$3" Then

    ActiveSheet.Unprotect

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    StartColumn = 17
    LastColumn = 112
    iRow = 1
    For i = StartColumn To LastColumn
    If Cells(iRow, i).Value <> "X" Then
    Cells(iRow, i).EntireColumn.Hidden = False
    Else
    Cells(iRow, i).EntireColumn.Hidden = True
    End If
    Next i

    Application.ScreenUpdating = True
    Application.EnableEvents = True

    ActiveSheet.Protect

    End If

    End Sub

    This macro only work's when the dropdown in this worksheet's cell B3 is changed manually.
    I have tried to change "If target.Address = "$B$3" Then" to "If Not Intersect (Target, Me.Range("B3")) Is Nothing Then" which isn't working.
    I have also tried to change the Worksheet_Change to Worksheet_Calculate and change "If target.Address = "$B$3" to

    Private Sub Worksheet_Calculate()

    Dim target As Range
    Set target = Range("B3")

    If Not Intersect(target, Range("B3")) Is Nothing Then

    which is also not working. Any help would be appreciated. Thanks in advance

  2. #2
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2408 and WPS V2024(12.1.0.18543)
    Posts
    3,834

    Re: VBA code to run when linked cell value changes

    Pls try this code

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    02-26-2024
    Location
    Houston, TX
    MS-Off Ver
    365
    Posts
    4

    Re: VBA code to run when linked cell value changes

    Thank you. This worked for a minute but kind of randomly, I got the error message "Unable to set the Hidden property of the Range class" and the row "Cells(iRow, i).EntireColumn.Hidden = True" was highlighted. Any thoughts?

  4. #4
    Forum Expert
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    O365 32bit (Windows)
    Posts
    2,521

    Re: VBA code to run when linked cell value changes

    I have created several worksheets with VBA code that hide columns that are not referenced in a drop down list of months.
    Try a workbook level change event.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    02-26-2024
    Location
    Houston, TX
    MS-Off Ver
    365
    Posts
    4

    Re: VBA code to run when linked cell value changes

    Thank you ByteMarks. I already have some code in the workbook module (below). I'm not sure how to combine what you provided above with what's already there. I'm also having some trouble understanding what items are changable variables in your code. I assume column #s and Sheet names?

    Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
    On Error GoTo ErrorOut
    Application.EnableEvents = False
    With ThisWorkbook
    Select Case sh.Name
    Case "Financial Graphs"
    .Sheets("Cost Code Chg").Range("B23") = .Sheets("Financial Graphs").Range("O3")
    .Sheets("Monthly Financial Input").Range("E2") = .Sheets("Cost Code Chg").Range("B23")
    .Sheets("Monthly Schedule Input").Range("B3") = .Sheets("Monthly Financial Input").Range("E2")
    Case "Cost Code Chg"
    .Sheets("Monthly Financial Input").Range("E2") = .Sheets("Cost Code Chg").Range("B23")
    .Sheets("Monthly Schedule Input").Range("B3") = .Sheets("Monthly Financial Input").Range("E2")
    .Sheets("Financial Graphs").Range("O3") = .Sheets("Monthly Schedule Input").Range("B3")
    Case "Monthly Financial Input"
    .Sheets("Monthly Schedule Input").Range("B3") = .Sheets("Monthly Financial Input").Range("E2")
    .Sheets("Financial Graphs").Range("O3") = .Sheets("Monthly Schedule Input").Range("B3")
    .Sheets("Cost Code Chg").Range("B23") = .Sheets("Financial Graphs").Range("O3")
    Case "Monthly Schedule Input"
    .Sheets("Financial Graphs").Range("O3") = .Sheets("Monthly Schedule Input").Range("B3")
    .Sheets("Cost Code Chg").Range("B23") = .Sheets("Financial Graphs").Range("O3")
    .Sheets("Monthly Financial Input").Range("E2") = .Sheets("Cost Code Chg").Range("B23")
    End Select
    End With
    ErrorOut:
    Application.EnableEvents = True
    End Sub

  6. #6
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2408 and WPS V2024(12.1.0.18543)
    Posts
    3,834

    Re: VBA code to run when linked cell value changes

    pls try this code

    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    O365 32bit (Windows)
    Posts
    2,521

    Re: VBA code to run when linked cell value changes

    assume column #s and Sheet names?
    s is an integer for looping through the array of worksheets called shts.
    You'll probably want to change this line to match your actual worksheet names.
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Change Cell name, linked to Sheet name, linked to VBA code
    By nik_re in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-01-2022, 08:06 AM
  2. Adding Code to ComboBox Breaks formula to linked cell??
    By willow1985 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-06-2019, 12:59 PM
  3. Help VBA Code for 2 cell linked calculations
    By jceast7 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-05-2018, 07:36 AM
  4. VBA code to disable A hyper-linked object dependent to a cell value
    By Ansh_09 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-22-2013, 03:55 AM
  5. How to get Workbook_SheetChange code to run from a ComboBox dropdown linked cell
    By Warfarin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-13-2013, 08:08 PM
  6. [SOLVED] Running code based on a change in a linked cell of a combo box
    By cdiaz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-13-2011, 02:34 PM
  7. [SOLVED] Code to show blank instead of 0 when there is no value in linked cell?
    By StargateFan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-21-2005, 09:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1