I have a command button's name (sunday) to change automatically when I change
the day on a different worksheet. I have since added six more command buttons
(one for each day of the week), however I do not know how to edit this code
below to include all the other command buttons. Any help would be appreciated
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Set myCell = Me.Range("A1")

If Target.Cells.Count 1 Then
Exit Sub 'one cell at a time
End If

If Intersect(Target, myCell) Is Nothing Then
Exit Sub
End If

Worksheets("sheet1").CommandButton1.Caption _
= Format(myCell.Value, "mmmm dd, yyyy")

End Sub

You'll have to change the addresses:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRngToCheck As Range
'I only used 3 cells and 3 commandbuttons
Set myRngToCheck = Me.Range("A1,b9,c3")

If Target.Cells.Count 1 Then
Exit Sub 'one cell at a time
End If

If Intersect(Target, myRngToCheck) Is Nothing Then
Exit Sub
End If

select case target.address(0,0)
'use uppercase addresses here and match the addresses above!

case is = "A1"
Worksheets("sheet1").CommandButton1.Caption _
= Format(target.Value, "mmmm dd, yyyy")

case is = "B9"
Worksheets("sheet1").CommandButton2.Caption _
= Format(target.Value, "mmmm dd, yyyy")

case is = "c3"
Worksheets("sheet1").CommandButton3.Caption _
= Format(target.Value, "mmmm dd, yyyy")

end select

End Sub

Compiled, but not tested.

aussiegirlone wrote:

I have a command button's name (sunday) to change automatically when I change
the day on a different worksheet. I have since added six more command buttons
(one for each day of the week), however I do not know how to edit this code
below to include all the other command buttons. Any help would be appreciated
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Set myCell = Me.Range("A1")

If Target.Cells.Count 1 Then
Exit Sub 'one cell at a time
End If

If Intersect(Target, myCell) Is Nothing Then
Exit Sub
End If

Worksheets("sheet1").CommandButton1.Caption _
= Format(myCell.Value, "mmmm dd, yyyy")

End Sub

--

Dave Peterson

Thank you very much Dave; this code works exactly the way I wanted it.
PS: I hope you had a good new year

"Dave Peterson" wrote:

You'll have to change the addresses:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRngToCheck As Range
'I only used 3 cells and 3 commandbuttons
Set myRngToCheck = Me.Range("A1,b9,c3")

If Target.Cells.Count 1 Then
Exit Sub 'one cell at a time
End If

If Intersect(Target, myRngToCheck) Is Nothing Then
Exit Sub
End If

select case target.address(0,0)
'use uppercase addresses here and match the addresses above!

case is = "A1"
Worksheets("sheet1").CommandButton1.Caption _
= Format(target.Value, "mmmm dd, yyyy")

case is = "B9"
Worksheets("sheet1").CommandButton2.Caption _
= Format(target.Value, "mmmm dd, yyyy")

case is = "c3"
Worksheets("sheet1").CommandButton3.Caption _
= Format(target.Value, "mmmm dd, yyyy")

end select

End Sub

Compiled, but not tested.

aussiegirlone wrote:

I have a command button's name (sunday) to change automatically when I change
the day on a different worksheet. I have since added six more command buttons
(one for each day of the week), however I do not know how to edit this code
below to include all the other command buttons. Any help would be appreciated
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Set myCell = Me.Range("A1")

If Target.Cells.Count 1 Then
Exit Sub 'one cell at a time
End If

If Intersect(Target, myCell) Is Nothing Then
Exit Sub
End If

Worksheets("sheet1").CommandButton1.Caption _
= Format(myCell.Value, "mmmm dd, yyyy")

End Sub

--

Dave Peterson

Glad you got it working--and avoided the lower case problem that I put in the
code.

case is = "c3"

Should have been:

case is = "C3"



aussiegirlone wrote:

Thank you very much Dave; this code works exactly the way I wanted it.
PS: I hope you had a good new year

"Dave Peterson" wrote:

You'll have to change the addresses:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRngToCheck As Range
'I only used 3 cells and 3 commandbuttons
Set myRngToCheck = Me.Range("A1,b9,c3")

If Target.Cells.Count 1 Then
Exit Sub 'one cell at a time
End If

If Intersect(Target, myRngToCheck) Is Nothing Then
Exit Sub
End If

select case target.address(0,0)
'use uppercase addresses here and match the addresses above!

case is = "A1"
Worksheets("sheet1").CommandButton1.Caption _
= Format(target.Value, "mmmm dd, yyyy")

case is = "B9"
Worksheets("sheet1").CommandButton2.Caption _
= Format(target.Value, "mmmm dd, yyyy")

case is = "c3"
Worksheets("sheet1").CommandButton3.Caption _
= Format(target.Value, "mmmm dd, yyyy")

end select

End Sub

Compiled, but not tested.

aussiegirlone wrote:

I have a command button's name (sunday) to change automatically when I change
the day on a different worksheet. I have since added six more command buttons
(one for each day of the week), however I do not know how to edit this code
below to include all the other command buttons. Any help would be appreciated
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Set myCell = Me.Range("A1")

If Target.Cells.Count 1 Then
Exit Sub 'one cell at a time
End If

If Intersect(Target, myCell) Is Nothing Then
Exit Sub
End If

Worksheets("sheet1").CommandButton1.Caption _
= Format(myCell.Value, "mmmm dd, yyyy")

End Sub

--

Dave Peterson


--

Dave Peterson