Topic: Continuing with code past a Msg Box

[krampaul82]

Hey Guys See code below

'---------------------------------------------------------------------------------------
' Module    : Module3_get_excell_files
' DateTime  : 11/23/2012 15:05
' Author    : Mark
' Purpose   : This module gets the needed Excel Files for the newly created Current Project
'---------------------------------------------------------------------------------------

Option Explicit

Sub gtc_move_Excell()

'Declare Varibles
 Dim sSourceDir As String
 Dim sBackDir As String
 Dim sNextFile As String
 Dim sDriveLetter As String
 Dim currpath As String
 Dim ws As String

'Dim gtcprojnum As Double
 On Error GoTo FileCopyError

'Change to The Excel Data to prepare for file move....
 ChDir "Excel Data"

'Change these paths to suit.
 sSourceDir = "V:\gtc_proj\Excel_Data\"

 sDriveLetter = Left$(CurDir$(), 1)
 currpath = CurDir$([sDriveLetter])


 sBackDir = gtcprojnum 'Global Varible set in Module 1

 sNextFile = Dir$(sSourceDir & "*.XLS")

 While sNextFile <> ""
    FileCopy sSourceDir & sNextFile, sBackDir & sNextFile
    sNextFile = Dir$
 Wend

 MsgBox "Excel Files W/Prefix are in place. On to the Data Extraction Files..."  (this is where I would like to continue on without the user having to hit OK) is this possible?
any help appreciated! 

[Keith™]

MsgBox is a modal dialog and as such stops the current execution while it is being displayed.
However, there are solutions, such as a timeout on the dialog which acts as if the user did press a button after the time has elapsed (not sure if it is available in AutoCAD vba), however, there are modeless forms which you can create and use.

Add a new form to your project and make it about the size of the msgbox dialog.
Place your desired labels and buttons on the form designing it as you like. You can add images to achieve the same look at the msgbox.
In your code, set the label values, title etc. to say what you want and then create a new form i.e.

Code: [Select]

'Create the form
set NewForm = New MyMsgBoxForm '(if MyMsgBoxForm is what you named your form)
With Newform
.Caption = "Message"
.label1.Text ="Excel Files W/Prefix are in place. On to the Data Extraction Files..."
.show(vbModeless)
End With

Whenever you want to hide the form during your code execution, simply call NewForm.Hide

If you create the form in one function and the variable goes out of scope, you won't be able to close it using the variable name, so you may need to create a property that holds the message form for you. For example:

Code: [Select]

Private _Myform as Userform
_Myform = New MyMsgBoxForm

Property Get MessageBox() As MyMsgBoxForm
set MessageBox = _Myform
End Property

Now you can call MessageBox to do the work.

Code: [Select]

With MessageBox
'do stuff here
End With

Of course I have not messed with vba for several years so I can't guarantee there are not changes in the VBA libraries or that I got the syntax right ... but the idea should work for you.