Same Value in ALL Sheets in a workbook, based on sheet1 Cell Value + Code Strange Behaviour

[Corey]

I have on going problems with a cell value (date), and have decided the best
way to go is to
place a value (date) in sheet4 range (E2), then reference all the other
sheets to have the same cell value as it.

How can i code this ?
I am sure it is fairly simple.
But cannot get it right.


PLUS i have an error in a textbox displayed on a userform that has the same
cell as a controlsource.

It displays the cell value in a (31 October 2006) formatted date,
but i have 2 other macros that add or subtract 7 days from the date vaue in
(E2).

When i add or subtract 7 days or more, the date changes when i open the
userform, with the textbox.

But i can add 7 days to 31 October 2006.
Cell (E2) displays - "7 November 2006", which is correct.
The textbox on the userform however displays "11 July 2006".

Strange???
***************************************************************
User form displays a Question "Is the Highlighted Date the Correct Date to
Use?"
Textbox Dsiplays cell value (E2) Date.
Then a ListBox with controlsource range"N1:N2" Yes or No can be selected.
***************************************************************

Userform Code below :
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub ListBox1_Click()

End Sub

Private Sub ComboBox1_Change()

End Sub

Private Sub CommandButton1_Click()
If ComboBox1 = Range("N1") Then ' <==== "N1" value = Yes
Userform1.Hide
Call PrintNewSheets
Else
If ComboBox1 = Range("N2") Then ' <==== "N2" value = No
MsgBox "Please UpDate the Date to the Correct Date using the Button
Provided, then Try Again.", , "Title...."
End If
End If
Userform1.Hide
End Sub

Private Sub CommandButton2_Click()
Userform1.Hide
End Sub

Private Sub Label1_Click()

End Sub

Private Sub TextBox1_Change()
With Me.TextBox1
.Text = Format(.Text, "dd mmmm yyyy")
End With
End Sub


Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()
Sheets("J Coleiro").Select
With Me.TextBox1
.ControlSource = "E2"
.Text = Format(.Text, "dd mmmm yyyy")
End With
Sheets("Enter - Exit").Select
End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Below are the codes for the adding/subtracting days to the date cell value,
in case they are causing the error.


Code for Adding 7 days i am using is:
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Sub callUpdates()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Application.Worksheets
ws.Select
With Range("E2")
..Value = Range("E2").Value + 7
Application.ScreenUpdating = False
End With
Next
Sheet4.Select
Application.ScreenUpdating = True
End Sub
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@


Code for subtracting 7 days i am using is:
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Sub callUnDoUpdates()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Application.Worksheets
ws.Select
With Range("E2")
..Value = Range("E2").Value - 7
Application.ScreenUpdating = False
End With
Next
Sheet4.Select
Application.ScreenUpdating = True
End Sub
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

 

[ADG]

Hi

Have you checked the windows regional and language settings are correct in
the control panel? I think that this may be causing your format issue. The
actual date is stored as an integer so this is likely to be correct.

 

[Corey]

I checked the regional setting on my PC but all is OK there.

Any other idea's?

Corey