unwanted message box

**CityMPLSEmpolyee** · 11-03-2010, 02:21 PM

I am tring to make a code that will ask the user when the file is opened to enter there name, only if there is no user already entered for Responsiblity of the file

but after the code is run it asks me to "Put Your Code Here" with a message box & OK button


Private Sub Workbook_Open()

Dim Name As String
 
If Range("d2").Value = "" Then Range("d2").Value = InputBox(Prompt:="Enter your name here if You are Responsible for Pasting this data into the project Plan Sheets (DGN'S)", Title:="ENTER YOUR NAME", Default:="")
End Sub

**ChemistB** · 11-03-2010, 04:47 PM

Your code should be pasted into the "This Workbook" module. Is that where you have it?

**royUK** · 11-04-2010, 05:37 AM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

**CityMPLSEmpolyee** · 11-04-2010, 08:44 AM

Yes my code is in the "This Workbook" module, and after I enter a name in it it returns with anther message box that I did not put in the code

**royUK** · 11-04-2010, 08:48 AM

You don't specify a sheet to check, if the workbook has a different sheet active when it is opened then that cell on the activesheet will be checked

**CityMPLSEmpolyee** · 11-04-2010, 09:00 AM

All the code that I’m am using is not listed, & I did not add that because some of it is opening a file for reference then displaying a message then closing the file, but before opening the file I am setting the worksheet to the "Raw Data" Tab


Private Sub Workbook_Open()

Sheets("Raw Data").Select
Range("D5").Select

Call open_File

MsgBox "Hello" & vbCr & _
"Excel has opened the" & vbCr & _
"Mpls_PW_Construction_Items_and_Prices.xls" & vbCr & _
"file to make sure your lookup data is Updated" & vbCr & _
"& now it will close the file" & vbCr & _
"Thanks for your Cooperation"

Call Close_File

If Range("d2").Value = "" Then Range("d2").Value = InputBox(Prompt:="Enter your name here if You are Responsible for Pasting this data into the project Plan Sheets (DGN'S)", Title:="ENTER YOUR NAME", Default:="")

End Sub

and in a macro named "Module_open_File" I have the following code


Sub open_File()

SaveLocation (False)

Workbooks.Open Filename:="http://cmean407/pw/0000/dms25500/Mpls_PW_Construction_Items_and_Prices.xls"

SaveLocation (True)
    
End Sub
Sub Close_File()

    Windows("Mpls_PW_Construction_Items_and_Prices.xls").Activate
    ActiveWorkbook.Close
    
End Sub
Public Sub SaveLocation(ReturnToLoc As Boolean)

Static WB As Workbook
Static WS As Worksheet
Static R As Range

If ReturnToLoc = False Then
    Set WB = ActiveWorkbook
    Set WS = ActiveSheet
    Set R = Selection
Else
    WB.Activate
    WS.Activate
    R.Select
End If

End Sub
'To save the current location, call SetSaveLoc.
Public Sub SetSaveLoc()
    SaveLocation (False)
End Sub
'To return to the saved location, call GetSaveLoc.
Public Sub GetSaveLoc()
    SaveLocation (True)
End Sub

**CityMPLSEmpolyee** · 11-04-2010, 09:05 AM

after I get these working, what I would like to added is, if there is a name entered in cell "D5" I would like the program to announce that This is the person responsible for the data and to communicate to them that you have changed/update some of the data

**royUK** · 11-04-2010, 09:06 AM

It doesn't mattere what you set the sheet to the critical line is not checking a specific sheet, or in this case it is even checking the newly opened file. You should run the check part before opening another file

**CityMPLSEmpolyee** · 11-04-2010, 09:16 AM

I isolated the code and ran it by its self and still got the extra message
Where in the code would you put the checking of a specific sheet?

And I will test moving the code line to the beginning of the code

**CityMPLSEmpolyee** · 11-04-2010, 09:38 AM

I am trying to attach the file,

I tried testing in a new excel file and it works with out giving the extra message.
So that means that I have some other error in the file what do you sagest?

Excel Quanitity work sheet V3.00.zip

**CityMPLSEmpolyee** · 11-04-2010, 09:51 AM

I am sorry to have bother you all I found my error I had code in the worksheet "Raw Data"

But if any one would have a suggestion of what to add to the code so that if the name is entered I get a message instead of the in put box,

See reply #7