Workbook_Open Sub editing wrong sheet

[Ajunk]

I am using this script to add Dates and a time stamp to the Sheet specified in the Script. It works, but it is also adding the Date and Time to a completely different worksheet. The name is not even close. I have to use this Format because Wonderware Historian uses this format to pull data out and this is how it reads tag values.

Once again, the script works great for the sheet that it is intended to, but I don't know why it is adding the Dates and times to the other sheet.

I'm sure this is a simple error and I am fairly new to VBA. Thank you for your help and please excuse any incompetence

Private Sub Workbook_Open()
Dim MeasData As Worksheet
Set MeasData = Worksheets("Measurement Data")
    MeasData.Range("A2").Value = Format(Now(), "MM/dd/yyyy 02:30:00 PM")
    MeasData.Range("A7").Value = Format(Now(), "MM/dd/yyyy 10:30:00 PM")
    MeasData.Range("A12").Value = Format(Now(), "MM/dd/yyyy 06:30:00 AM")

End Sub

 

[Norie]

Is there any other code in the workbook?

 

[Ajunk]

Yes.
There are some ActiveX command buttons that allow user input into specified ranges.
And one button that adds the date to a range (a different range than the one that is added by the above code), saves the workbook, and prints.

All of that script is contained within the worksheet. And it is named completely different from the worksheet specified in the workbook_open sub.

I'll be glad to add this script if it will be helpful but it is rather large because there are 9 ActiveX buttons that do
3 differnt commands. 3 buttons for each of the 3 users.

 

[Joe4]

Are you sure that there are not any procedures in any of the Sheet modules that begin with the name "Worksheet_..."?

 

[Ajunk]

Here is all of the script: Contained in the worksheet that is being erroneously edited:

Private Sub ACHS_Click()
    Dim MyValue As Variant
        MyValue = InputBox("Change Hog's Beginning Measurement.... Come on")
        ActiveSheet.Unprotect Password:="Esme9939"
        Range("B5").Value = MyValue
        ActiveSheet.Protect Password:="Esme9939"
        ActiveWorkbook.Save
        
End Sub
Private Sub ACVS_Click()
    Dim MyValue As Variant
           MyValue = InputBox("Change Vertical's Beginning Measurement.... Come on")
           ActiveSheet.Unprotect Password:="Esme9939"
           Range("F5").Value = MyValue
           ActiveSheet.Protect Password:="Esme9939"
           ActiveWorkbook.Save
End Sub
Private Sub APS_Click()
    With ActiveSheet
        Unprotect Password:="Esme9939"
        Range("G1").Value = Format(Date, "Short Date")
        Range("A1:G15").PrintOut
        ActiveWorkbook.Save
        Protect Password:="Esme9939"
    End With
End Sub
 
Private Sub BCHS_Click()
Dim MyValue As Variant
        MyValue = InputBox("Change Hog's Beginning Measurement.... Come on")
        ActiveSheet.Unprotect Password:="Esme9939"
        Range("B20").Value = MyValue
        ActiveSheet.Protect Password:="Esme9939"
        ActiveWorkbook.Save
End Sub
Private Sub BCVS_Click()
Dim MyValue As Variant
           MyValue = InputBox("Change Vertical's Beginning Measurement.... Come on")
           ActiveSheet.Unprotect Password:="Esme9939"
           Range("F20").Value = MyValue
           ActiveSheet.Protect Password:="Esme9939"
           ActiveWorkbook.Save
End Sub
Private Sub BPS_Click()
With ActiveSheet
        Unprotect Password:="Esme9939"
        Range("G16").Value = Format(Date, "Short Date")
        Range("A16:G30").PrintOut
        ActiveWorkbook.Save
        Protect Password:="Esme9939"
    End With
End Sub
Private Sub CCHS_Click()
Dim MyValue As Variant
        MyValue = InputBox("Change Hog's Beginning Measurement.... Come on")
        ActiveSheet.Unprotect Password:="Esme9939"
        Range("B35").Value = MyValue
        ActiveSheet.Protect Password:="Esme9939"
        ActiveWorkbook.Save
End Sub
Private Sub CCVS_Click()
Dim MyValue As Variant
           MyValue = InputBox("Change Vertical's Beginning Measurement.... Come on")
           ActiveSheet.Unprotect Password:="Esme9939"
           Range("F35").Value = MyValue
           ActiveSheet.Protect Password:="Esme9939"
           ActiveWorkbook.Save
End Sub
Private Sub CommandButton1_Click()
Dim HogA As Variant, VertA As Variant
Dim HogRound As Variant, VertRound As Variant
Dim Rnd As Integer
    Rnd = 1
Set HogA = Worksheets("Measurement Data").Range("C4")
Set VertA = Worksheets("Measurement Data").Range("D4")
    
    HogRound = Round(HogA / Rnd)
    HogA = HogRound * Rnd
    
    VertRound = Round(VertA / Rnd)
    VertA = VertRound * Rnd
MsgBox "Hog Start  " & HogA & "     Vertical Start  " & VertA
End Sub
Private Sub CPS_Click()
With ActiveSheet
        Unprotect Password:="Esme9939"
        Range("G31").Value = Format(Date, "Short Date")
        Range("A31:G44").PrintOut
        
            Call WW_Daily_Usage
        
        ActiveWorkbook.Save
        Protect Password:="Esme9939"
    End With
End Sub

The unnamed Commandbutton1 is just a test to retrieve data from ranges in the "Measurement Data" Worksheet. It also works great.


Here is a module that makes a macro-free copy and saves it to a specific file: This Macro is Called by the CPS_Click.

Sub WW_Daily_Usage()
With Sheets("Weather Watch")
    Range("A1:G44").Select
    Selection.Copy
End With
    Workbooks.Add
    ActiveSheet.Paste
        ChDir "S:\Modbit\Compound Usage\WW 24 hr Compound"
        ActiveWorkbook.SaveAs Filename:= _
        "S:\Modbit\Compound Usage\WW 24 hr Compound\" & Format(Now(), "MMM,dd,yyyy") & ".xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Range("B1").Select
    ActiveWindow.Close
With Sheets("Weather Watch")
    Range("A4").Select
End With
End Sub

Once again, all of the above is working great, but code from the original post is adding the dates and times to the worksheet("Measurement Data") and ("Weather Watch"). Dunno why either.