Consulting

Results 1 to 10 of 10

Thread: Solved: Msgbox

  1. #1
    VBAX Tutor
    Joined
    Dec 2008
    Posts
    244
    Location

    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
    Last edited by lucas; 12-08-2008 at 08:47 AM. Reason: VBA tags added to code

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    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]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    chungtinhlak, when posting code, select it and hit the VBA button to format it as above.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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]

  5. #5
    VBAX Tutor
    Joined
    Dec 2008
    Posts
    244
    Location
    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]

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    You can actually be more explicit and use

    [vba]

    CreateObject("WScript.Shell").SpecialFolders("MyDocuments")
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    VBAX Tutor
    Joined
    Dec 2008
    Posts
    244
    Location
    So the WScript.Shell will actuall located teh nt username that is active? am i right?

  9. #9
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    It will load when you login, using the logged in user.

    So if many users are logged in, it will load for each user.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  10. #10
    VBAX Tutor
    Joined
    Dec 2008
    Posts
    244
    Location
    thank you so much. You guys are great.....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •