The following code works fine; except for the Userform1 I created which
is to serve as a Progress-Bar - "Macro Is Running" is content of
Textbox1- and
I have in the form code window Userform1.Initilize event Userform1.Show
vbModeless << The problem is when I Run the Macro the Userform1 comes
up but remains blank (without test) throughout.. In step-thru mode it
shows up.
I'm confused as to why it doesn't show the text while the macro is
successfully running.
Any ideas?
TIA,

Sub ExtractDataFromFiles()
Const sPath = "C:\Documents and Settings\Jim May\My Documents\"
Dim sName As String
Dim wb As Workbook
Dim j As Integer
Dim n As Integer
Dim r(1 To 14) As Variant
ActiveSheet.Range("A6:N2000").ClearContents
Load UserForm1

Application.ScreenUpdating = False
Application.DisplayAlerts = False
sName = Dir(sPath & "*.xls")
j = 6 ' Data starts on Row 6
Do While sName < ""
Set wb = Workbooks.Open(sPath & sName)
With wb.Worksheets("Cost Analysis")
r(1) = .Range("J2").Value
r(2) = .Range("B4").Value
r(3) = .Range("B6").Value
r(4) = .Range("G4").Value
r(5) = .Range("G6").Value
r(6) = .Range("G6").Value
r(7) = .Range("J1").Value
r(8) = .Range("G51").Value
r(9) = .Range("G53").Value
r(10) = .Range("G54").Value
r(11) = .Range("G56").Value
r(12) = .Range("G57").Value
r(13) = .Range("G58").Value
r(14) = .Range("G59").Value
End With
wb.Close SaveChanges:=False
With ThisWorkbook.ActiveSheet
For n = 1 To 14
..Cells(j, n).Value = r(n)
Next n
End With
j = j + 1
sName = Dir
Loop

UserForm1.Hide
Unload UserForm1
End Sub

Jim,
Maybe it's me, but I don't see where you're updating the textbox anyway ??
Anyway...
Add a DoEvents after you update the value of the text box, as you are
currently not giving the form chance to repaint.

NickHK

"Jim May" wrote in message
news:RBaAg.105576$IZ2.41608@dukeread07...
The following code works fine; except for the Userform1 I created which
is to serve as a Progress-Bar - "Macro Is Running" is content of
Textbox1- and
I have in the form code window Userform1.Initilize event Userform1.Show
vbModeless << The problem is when I Run the Macro the Userform1 comes
up but remains blank (without test) throughout.. In step-thru mode it
shows up.
I'm confused as to why it doesn't show the text while the macro is
successfully running.
Any ideas?
TIA,

Sub ExtractDataFromFiles()
Const sPath = "C:\Documents and Settings\Jim May\My Documents\"
Dim sName As String
Dim wb As Workbook
Dim j As Integer
Dim n As Integer
Dim r(1 To 14) As Variant
ActiveSheet.Range("A6:N2000").ClearContents
Load UserForm1

Application.ScreenUpdating = False
Application.DisplayAlerts = False
sName = Dir(sPath & "*.xls")
j = 6 ' Data starts on Row 6
Do While sName < ""
Set wb = Workbooks.Open(sPath & sName)
With wb.Worksheets("Cost Analysis")
r(1) = .Range("J2").Value
r(2) = .Range("B4").Value
r(3) = .Range("B6").Value
r(4) = .Range("G4").Value
r(5) = .Range("G6").Value
r(6) = .Range("G6").Value
r(7) = .Range("J1").Value
r(8) = .Range("G51").Value
r(9) = .Range("G53").Value
r(10) = .Range("G54").Value
r(11) = .Range("G56").Value
r(12) = .Range("G57").Value
r(13) = .Range("G58").Value
r(14) = .Range("G59").Value
End With
wb.Close SaveChanges:=False
With ThisWorkbook.ActiveSheet
For n = 1 To 14
.Cells(j, n).Value = r(n)
Next n
End With
j = j + 1
sName = Dir
Loop

UserForm1.Hide
Unload UserForm1
End Sub

Hi NickHK,

I Added 2 lines within the loop:

DoEvents
Userform1.Repaint

And everything now works fine. I'm creating this macro for a
Business friend who runs his Excel stuff on Macs, not PCs. I'm
Just rocking along coding what I think he needs (here on my PC),
Hoping everything I do will "transfer" without a hitch to his
Mac based units and his Excel program. Do I need to be concerned
About anything in particular (that you know of) here?

Thanks again..

Jim

"NickHK" wrote in message
:

Jim,
Maybe it's me, but I don't see where you're updating the textbox anyway ??
Anyway...
Add a DoEvents after you update the value of the text box, as you are
currently not giving the form chance to repaint.

NickHK

"Jim May" wrote in message
news:RBaAg.105576$IZ2.41608@dukeread07...

The following code works fine; except for the Userform1 I created which
is to serve as a Progress-Bar - "Macro Is Running" is content of
Textbox1- and
I have in the form code window Userform1.Initilize event Userform1.Show
vbModeless << The problem is when I Run the Macro the Userform1 comes
up but remains blank (without test) throughout.. In step-thru mode it
shows up.
I'm confused as to why it doesn't show the text while the macro is
successfully running.
Any ideas?
TIA,

Sub ExtractDataFromFiles()
Const sPath = "C:\Documents and Settings\Jim May\My Documents\"
Dim sName As String
Dim wb As Workbook
Dim j As Integer
Dim n As Integer
Dim r(1 To 14) As Variant
ActiveSheet.Range("A6:N2000").ClearContents
Load UserForm1

Application.ScreenUpdating = False
Application.DisplayAlerts = False
sName = Dir(sPath & "*.xls")
j = 6 ' Data starts on Row 6
Do While sName < ""
Set wb = Workbooks.Open(sPath & sName)
With wb.Worksheets("Cost Analysis")
r(1) = .Range("J2").Value
r(2) = .Range("B4").Value
r(3) = .Range("B6").Value
r(4) = .Range("G4").Value
r(5) = .Range("G6").Value
r(6) = .Range("G6").Value
r(7) = .Range("J1").Value
r(8) = .Range("G51").Value
r(9) = .Range("G53").Value
r(10) = .Range("G54").Value
r(11) = .Range("G56").Value
r(12) = .Range("G57").Value
r(13) = .Range("G58").Value
r(14) = .Range("G59").Value
End With
wb.Close SaveChanges:=False
With ThisWorkbook.ActiveSheet
For n = 1 To 14
.Cells(j, n).Value = r(n)
Next n
End With
j = j + 1
sName = Dir
Loop

UserForm1.Hide
Unload UserForm1
End Sub

Jim,
I don't work on Macs, but bear in mind that as this is not Windows, there is
no ActiveX. I assume all the native Excel Forms controls function, but I
have no idea about those from the Control Toolbox.
I would think any other controls would be a no-no.
I have no idea how Office automation would work, as there is no COM/OLE.

I guess keep it simple.

I just read that according to http://en.wikipedia.org/wiki/Microsoft_Excel :
"The first version of Excel was released for the Mac in 1985..."

This may help:
http://www.microsoft.com/mac/product...usingexcel2004

NickHK

"Jim May" wrote in message
news:i%jAg.105594$IZ2.45693@dukeread07...
Hi NickHK,

I Added 2 lines within the loop:

DoEvents
Userform1.Repaint

And everything now works fine. I'm creating this macro for a
Business friend who runs his Excel stuff on Macs, not PCs. I'm
Just rocking along coding what I think he needs (here on my PC),
Hoping everything I do will "transfer" without a hitch to his
Mac based units and his Excel program. Do I need to be concerned
About anything in particular (that you know of) here?

Thanks again..

Jim

"NickHK" wrote in message
:

Jim,
Maybe it's me, but I don't see where you're updating the textbox anyway
??
Anyway...
Add a DoEvents after you update the value of the text box, as you are
currently not giving the form chance to repaint.

NickHK

"Jim May" wrote in message
news:RBaAg.105576$IZ2.41608@dukeread07...

The following code works fine; except for the Userform1 I created
which
is to serve as a Progress-Bar - "Macro Is Running" is content of
Textbox1- and
I have in the form code window Userform1.Initilize event
Userform1.Show
vbModeless << The problem is when I Run the Macro the Userform1
comes
up but remains blank (without test) throughout.. In step-thru mode it
shows up.
I'm confused as to why it doesn't show the text while the macro is
successfully running.
Any ideas?
TIA,

Sub ExtractDataFromFiles()
Const sPath = "C:\Documents and Settings\Jim May\My Documents\"
Dim sName As String
Dim wb As Workbook
Dim j As Integer
Dim n As Integer
Dim r(1 To 14) As Variant
ActiveSheet.Range("A6:N2000").ClearContents
Load UserForm1

Application.ScreenUpdating = False
Application.DisplayAlerts = False
sName = Dir(sPath & "*.xls")
j = 6 ' Data starts on Row 6
Do While sName < ""
Set wb = Workbooks.Open(sPath & sName)
With wb.Worksheets("Cost Analysis")
r(1) = .Range("J2").Value
r(2) = .Range("B4").Value
r(3) = .Range("B6").Value
r(4) = .Range("G4").Value
r(5) = .Range("G6").Value
r(6) = .Range("G6").Value
r(7) = .Range("J1").Value
r(8) = .Range("G51").Value
r(9) = .Range("G53").Value
r(10) = .Range("G54").Value
r(11) = .Range("G56").Value
r(12) = .Range("G57").Value
r(13) = .Range("G58").Value
r(14) = .Range("G59").Value
End With
wb.Close SaveChanges:=False
With ThisWorkbook.ActiveSheet
For n = 1 To 14
.Cells(j, n).Value = r(n)
Next n
End With
j = j + 1
sName = Dir
Loop

UserForm1.Hide
Unload UserForm1
End Sub

Thanks again NickHK

"NickHK" wrote in message
:

Jim,
I don't work on Macs, but bear in mind that as this is not Windows, there is
no ActiveX. I assume all the native Excel Forms controls function, but I
have no idea about those from the Control Toolbox.
I would think any other controls would be a no-no.
I have no idea how Office automation would work, as there is no COM/OLE.

I guess keep it simple.

I just read that according to http://en.wikipedia.org/wiki/Microsoft_Excel :
"The first version of Excel was released for the Mac in 1985..."

This may help:
http://www.microsoft.com/mac/product...usingexcel2004

NickHK

"Jim May" wrote in message
news:i%jAg.105594$IZ2.45693@dukeread07...

Hi NickHK,

I Added 2 lines within the loop:

DoEvents
Userform1.Repaint

And everything now works fine. I'm creating this macro for a
Business friend who runs his Excel stuff on Macs, not PCs. I'm
Just rocking along coding what I think he needs (here on my PC),
Hoping everything I do will "transfer" without a hitch to his
Mac based units and his Excel program. Do I need to be concerned
About anything in particular (that you know of) here?

Thanks again..

Jim

"NickHK" wrote in message
:


Jim,
Maybe it's me, but I don't see where you're updating the textbox anyway

??

Anyway...
Add a DoEvents after you update the value of the text box, as you are
currently not giving the form chance to repaint.

NickHK

"Jim May" wrote in message
news:RBaAg.105576$IZ2.41608@dukeread07...


The following code works fine; except for the Userform1 I created

which

is to serve as a Progress-Bar - "Macro Is Running" is content of
Textbox1- and
I have in the form code window Userform1.Initilize event

Userform1.Show

vbModeless << The problem is when I Run the Macro the Userform1

comes

up but remains blank (without test) throughout.. In step-thru mode it
shows up.
I'm confused as to why it doesn't show the text while the macro is
successfully running.
Any ideas?
TIA,

Sub ExtractDataFromFiles()
Const sPath = "C:\Documents and Settings\Jim May\My Documents\"
Dim sName As String
Dim wb As Workbook
Dim j As Integer
Dim n As Integer
Dim r(1 To 14) As Variant
ActiveSheet.Range("A6:N2000").ClearContents
Load UserForm1

Application.ScreenUpdating = False
Application.DisplayAlerts = False
sName = Dir(sPath & "*.xls")
j = 6 ' Data starts on Row 6
Do While sName < ""
Set wb = Workbooks.Open(sPath & sName)
With wb.Worksheets("Cost Analysis")
r(1) = .Range("J2").Value
r(2) = .Range("B4").Value
r(3) = .Range("B6").Value
r(4) = .Range("G4").Value
r(5) = .Range("G6").Value
r(6) = .Range("G6").Value
r(7) = .Range("J1").Value
r(8) = .Range("G51").Value
r(9) = .Range("G53").Value
r(10) = .Range("G54").Value
r(11) = .Range("G56").Value
r(12) = .Range("G57").Value
r(13) = .Range("G58").Value
r(14) = .Range("G59").Value
End With
wb.Close SaveChanges:=False
With ThisWorkbook.ActiveSheet
For n = 1 To 14
.Cells(j, n).Value = r(n)
Next n
End With
j = j + 1
sName = Dir
Loop

UserForm1.Hide
Unload UserForm1
End Sub