Solved: Msgbox

Hello, I have to open a file from the local hard drive to perform some calculation. Because everyone as a unique my document folder. Therefore, I set up the following.

[vba]
Dim wkbPrime As Workbook, wkbMacro As Workbook, wkbCallSheet As Workbook, _
strNTuser As String
Set wkbPrime = Application.Workbooks.Open _
("C:\Documents and Settings\" & UserForm1.txtntuser & "\My Documents\frmPrime.xls")
Set wkbMacro = Application.Workbooks("User Form.xls")
Set wkbCallSheet = Application.Workbooks.Open _
("\\fil-nw-312\QA_787_Vis\Daily Metrics\Call_sheet\Call-Sheet-Analysis.xls")
[/vba]
The text box allow user to input there user name in and it will locate the correct path. however, if I input the wrong one, it will give me this error (attached picture).

I want to create an if state to to display a message box to let them know that the nt user name they input is incorrect and give them a chance to input a new one. Can you guys help me.

thanks in advance

Ditch the textbox, get it directly

[vba]

Dim wkbPrime As Workbook, wkbMacro As Workbook, wkbCallSheet As Workbook, _
strNTuser As String
Dim filepath As String

filepath = CreateObject("WScript.Shell").SpecialFolders(16)

Set wkbPrime = Application.Workbooks.Open(filepath & "\frmPrime.xls")
Set wkbMacro = Application.Workbooks("User Form.xls")
Set wkbCallSheet = Application.Workbooks.Open _
("\\fil-nw-312\QA_787_Vis\Daily Metrics\Call_sheet\Call-Sheet-Analysis.xls")
[/vba]

chungtinhlak, when posting code, select it and hit the VBA button to format it as above.

Bob's method would be best.

I would advise that you add some error routine or account for potential errors though. e.g.
[vba]
Sub Test()
Dim wkbPrime As Workbook, wkbMacro As Workbook, wkbCallSheet As Workbook
Dim strNTuser As String, filepath As String

filepath = CreateObject("WScript.Shell").SpecialFolders(16)
'filepath = "C:\Documents and Settings\" & Environ("username") & "\My Documents\" & frmPrime.xls"

Set wkbPrime = WBOpen(filepath & "\frmPrime.xls", wkbPrime)
Set wkbMacro = WBOpen("User Form.xls")
Set wkbCallSheet = WBOpen("\\fil-nw-312\QA_787_Vis\Daily Metrics\Call_sheet\Call-Sheet-Analysis.xls")
End Sub

Function WBOpen(WBName As String, Optional wb As Workbook) As Workbook
If IsWorkbookOpen(WBName) Then Exit Function
If FileExists(WBName) Then
Set wb = Workbooks.Open(WBName)
Else: MsgBox WBName & " does not exist.", vbCritical, "Error"
Exit Function
End If
Set WBOpen = wb
End Function

Function FileExists(sFilename As String) As Boolean
Dim fso As Object, tf As Boolean
Set fso = CreateObject("Scripting.FileSystemObject")
tf = fso.FileExists(sFilename)
Set fso = Nothing
FileExists = tf
End Function

Function IsWorkbookOpen(stName As String) As Boolean
Dim Wkb As Workbook
On Error Resume Next ' In Case it isn't Open
Set Wkb = Workbooks(stName)
If Not Wkb Is Nothing Then IsWorkbookOpen = True
'Boolean Function assumed To be False unless Set To True
End Function
[/vba]

XLD,

Thanks for you help, I'm quite new at this, can I as you what this does? Specially with the "WScript.Shell")???

thanks

[VBA]
filepath = CreateObject("WScript.Shell").SpecialFolders(16)
[/VBA]

It calls into the Windows Script Host SpecialFolders method. SpecialFolders is a method to get any number of windows folders, which presumably Windows Script Host loads when it starts up, 16 is the MyDocuments folder.

You can actually be more explicit and use

[vba]

CreateObject("WScript.Shell").SpecialFolders("MyDocuments")
[/vba]

So the WScript.Shell will actuall located teh nt username that is active? am i right?

It will load when you login, using the logged in user.

So if many users are logged in, it will load for each user.

thank you so much. You guys are great.....