PDA

View Full Version : attending the end of macro....



sal21
09-10-2004, 07:39 AM
I have a mcro it import a txt file and put the data in a varous cells.
My problem is make appear on the active sheet a msg box "...attending macro working" in this mode the user not can write accidentally and disappear the msg box when the macro is finished.
I hope to be clear...

Sub ImportData()
Dim i As Long
Dim InFile As String, Str As String
Dim FileNum As Integer
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error GoTo AbEnd
i = 1
FileNum = FreeFile
InFile = "C:\LETTO"
Open InFile For Input As FileNum
With ThisWorkbook.Worksheets("FOGLIO1")
While Not EOF(FileNum)
NextRec:
Line Input #FileNum, Str
LineLen = Len(Str)
Loop1:
.Cells(i, 1) = Left(Str, 4)
.Cells(i, 2) = Mid(Str, 5, 7)
.Cells(i, 3) = Mid(Str, 13, 23)
.Cells(i, 4) = Mid(Str, 36, 1)
.Cells(i, 5) = Mid(Str, 38, 10)
.Cells(i, 6) = Mid(Str, 48, 11)
.Cells(i, 7) = Mid(Str, 59, 3)
.Cells(i, 8) = Mid(Str, 61, 8)
.Cells(i, 9) = Mid(Str, 69, 9)
.Cells(i, 10) = Mid(Str, 79, 10)
.Cells(i, 11) = Mid(Str, 91, 11)
.Cells(i, 12) = Mid(Str, 102, 11)
If (Len(Str) < 1) Then
GoTo NextRec
Else
'CALCOLARE LA LUNGHEZZA MASSIMA DELLA RIGA DA IMPORTARE IN QUESTO CASO 279 CARATTERI
Str = Mid(Str, 279)
End If
i = i + 1
GoTo Loop1
Wend
End With
AbEnd:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

smozgur
09-10-2004, 08:24 AM
Using Interactive and StatusBar properties might help better. User will be restricted to use the Excel Application when we set Interactive to False and will be also warned by the status bar text during macro execution.

Sub ImportData()
Dim i As Long
Dim InFile As String, Str As String
Dim FileNum As Integer

Application.StatusBar = "Please wait while macro is working..."
Application.Interactive = False

Application.ScreenUpdating = False
Application.EnableEvents = False

....
.... Other lines in original code
....

AbEnd:
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Interactive = True
Application.StatusBar = False
End Sub

I hope it helps.
Suat

sal21
09-10-2004, 01:45 PM
Using Interactive and StatusBar properties might help better. User will be restricted to use the Excel Application when we set Interactive to False and will be also warned by the status bar text during macro execution.

Sub ImportData()
Dim i As Long
Dim InFile As String, Str As String
Dim FileNum As Integer

Application.StatusBar = "Please wait while macro is working..."
Application.Interactive = False

Application.ScreenUpdating = False
Application.EnableEvents = False

....
.... Other lines in original code
....

AbEnd:
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Interactive = True
Application.StatusBar = False
End Sub

I hope it helps.
Suat
Tks for your help, but in this mode the user must change the data present in the sheet...