Name of cells in vba across sheets-CodePudding

Can someone help me with the following scenario:

If A1 in sheet1 is equal to "W1" how do I make a VBA so B2 in sheet2 is equal to "Goodmorning Monday".
I have to make so if A1 in Sheet1 is changed to "W2" then B2 in sheet2 is equal to "Goodmorning Tuesday".

I have tried the following code:

Sub Datesss()

If Sheets("Sheet1").Range("A1") = "W1" Then
Sheets("Sheet2").Range("B2") = "Goodmorning Monday"

If Sheets("Sheet1").Range("A1") = "W2" Then
Sheets("Sheet2").Range("B2") = "Goodmorning Tuesday"

End Sub

CodePudding user response：

A Worksheet Change: As Simple As It Gets

Copy the following code to the sheet code module of worksheet Sheet1. In the VBE Project Explorer, the name not in parentheses is the sheet code name which will also be displayed in the window in VBA, while the name in parentheses is the tab name.
There is nothing to run, it runs automatically.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim WDays(): WDays = VBA.Array("W1", "W2", "W3", "W4", "W5", "W6", "W7")
    Dim Days(): Days = VBA.Array("Monday", "Tuesday", "Wednesday", _
        "Thursday", "Friday", "Saturday", "Sunday") _
    
    Dim sws As Worksheet: Set sws = Me ' worksheet containing this code
    Dim sCell As Range: Set sCell = sws.Range("A1")
    
    Dim tCell As Range: Set tCell = Intersect(sCell, Target)
    If tCell Is Nothing Then Exit Sub
    
    Dim tValue As Variant: tValue = tCell.Value
    Dim tIndex As Variant: tIndex = Application.Match(tValue, WDays, 0)
    If IsError(tIndex) Then Exit Sub
    
    Dim dws As Worksheet: Set dws = sws.Parent.Sheets("Sheet2")
    Dim dCell As Range: Set dCell = dws.Range("B2")
    
    dCell.Value = "Good Morning " & Days(tIndex - 1)

End Sub

CodePudding user response：

Please, copy the next code in "Sheet1" code module:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.address(0, 0) = "A1" Then
        Dim sh2 As Worksheet: Set sh2 = Worksheets("Sheet2")
        If Target.Value Like "W?" Then
            If CLng(Mid(Target.Value, 2)) > 0 And CLng(Mid(Target.Value, 2)) <= 7 Then
                sh2.Range("B2").Value = "Goodmorning " & getDayName(Mid(Target.Value, 2)) 'Monday"
            End If
        End If
    End If
End Sub

Function getDayName(dayNo As Long) As String
    Dim arrDaysName: arrDaysName = Split("Monday,Tuesday,Wednesday,Thurstay,Friday,Saturday,Sunday", ",")
    getDayName = arrDaysName(CLng(dayNo))
End Function

Change "A1" cell content and check what happens in "Sheet2", "B2" cell...

Of course, it will do something only for suffix numbers between 1 and 7...

CodePudding user response：

You can use the built in functions in Code behind sheet. Goto your VBA projects (Alt F11), and then open the Project Explorer (Ctrl R) Double Click Sheet1 and code area will show. Then paste this code in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
    Worksheets("Sheet2").Cells(2, 2) = "Goodmorning Monday"
End If
End Sub

Whenever any Cell in Sheet1 is changed this code runs. And by checking the Target. Address you can manipulate what ever you want. Remember to save your file as .xlsm ;-)