Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
pls help ammend progress bar code
i'm trying to make this run automatically -
it was originally part of a demo written by Andy Pope where the userform was already showing & you had to push the button to start it. the form will show, but nothing happens until you click the checkbox to show the % values. then it runs. i haven't changed any of the code except to add some "call" commands to try to make it work, and removed the "private" from some of the subs (which i can replace if necessary as i have the original code in another workbook). without having the workbook & userform i know it's trickier, but maybe somebody sees something i need to do........... thanks in advance! susan xxxxxxxxxxxxxxxxxxxxxxxxx Option Explicit Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Const PI = 3.14159265358979 Sub DemoProgress1() ' ' Progress Bar Dim intIndex As Integer Dim sngPercent As Single Dim intMax As Integer intMax = 100 For intIndex = 1 To intMax sngPercent = intIndex / intMax ProgressStyle1 sngPercent, chkPg1Value.Value DoEvents '------------------------ ' Your code would go here '------------------------ Sleep 100 Next Unload Me 'added by susan End Sub Sub LoadHelp() Dim strMsg As String ' Progress Bar strMsg = " " & vbLf strMsg = " Please wait while the information is exported." & vbLf strMsg = strMsg & " " & vbLf strMsg = strMsg & " The progress indicator will vanish" & vbLf strMsg = strMsg & " when the exportation is completed." & vbLf labHelp1.Caption = strMsg End Sub Sub ProgressStyle1(Percent As Single, ShowValue As Boolean) ' ' Progress Style 1 ' Label Over Label ' Const PAD = " " If ShowValue Then labPg1v.Caption = PAD & Format(Percent, "0%") labPg1va.Caption = labPg1v.Caption labPg1va.Width = labPg1.Width End If labPg1.Width = Int(labPg1.Tag * Percent) End Sub Sub chkPg1Value_Click() labPg1v.Visible = chkPg1Value.Value labPg1va.Visible = chkPg1Value.Value Call run_the_progress_meter 'added by susan End Sub Sub UserForm_Initialize() ' ProgressBar1 labPg1.Tag = labPg1.Width labPg1.Width = 0 labPg1v.Caption = "" labPg1va.Caption = "" Call LoadHelp End Sub Sub run_the_progress_meter() Application.Cursor = xlWait Call DemoProgress1 Application.Cursor = xlDefault End Sub xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
pls help ammend progress bar code
Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Const PI = 3.14159265358979 Sub DemoProgress1() ' ' Progress Bar Dim intIndex As Integer Dim sngPercent As Single Dim intMax As Integer intMax = 100 For intIndex = 1 To intMax sngPercent = intIndex / intMax ProgressStyle1 sngPercent, True DoEvents '------------------------ ' Your code would go here '------------------------ Sleep 100 Next Unload Me 'added by susan End Sub Sub LoadHelp() Dim strMsg As String ' Progress Bar strMsg = " " & vbLf strMsg = " Please wait while the information is exported." & vbLf strMsg = strMsg & " " & vbLf strMsg = strMsg & " The progress indicator will vanish" & vbLf strMsg = strMsg & " when the exportation is completed." & vbLf labHelp1.Caption = strMsg End Sub Sub ProgressStyle1(Percent As Single, ShowValue As Boolean) ' ' Progress Style 1 ' Label Over Label ' Const PAD = " " If ShowValue Then labPg1v.Caption = PAD & Format(Percent, "0%") labPg1va.Caption = labPg1v.Caption labPg1va.Width = labPg1.Width End If labPg1.Width = Int(labPg1.Tag * Percent) End Sub Private Sub Userform_Activate() labPg1v.Visible = True labPg1va.Visible = True Call run_the_progress_meter 'added by susan End Sub Private Sub UserForm_Initialize() ' ProgressBar1 labPg1.Tag = labPg1.Width labPg1.Width = 0 labPg1v.Caption = "" labPg1va.Caption = "" Call LoadHelp End Sub Sub run_the_progress_meter() Application.Cursor = xlWait Call DemoProgress1 Application.Cursor = xlDefault End Sub -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Susan" wrote in message oups.com... i'm trying to make this run automatically - it was originally part of a demo written by Andy Pope where the userform was already showing & you had to push the button to start it. the form will show, but nothing happens until you click the checkbox to show the % values. then it runs. i haven't changed any of the code except to add some "call" commands to try to make it work, and removed the "private" from some of the subs (which i can replace if necessary as i have the original code in another workbook). without having the workbook & userform i know it's trickier, but maybe somebody sees something i need to do........... thanks in advance! susan xxxxxxxxxxxxxxxxxxxxxxxxx Option Explicit Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Const PI = 3.14159265358979 Sub DemoProgress1() ' ' Progress Bar Dim intIndex As Integer Dim sngPercent As Single Dim intMax As Integer intMax = 100 For intIndex = 1 To intMax sngPercent = intIndex / intMax ProgressStyle1 sngPercent, chkPg1Value.Value DoEvents '------------------------ ' Your code would go here '------------------------ Sleep 100 Next Unload Me 'added by susan End Sub Sub LoadHelp() Dim strMsg As String ' Progress Bar strMsg = " " & vbLf strMsg = " Please wait while the information is exported." & vbLf strMsg = strMsg & " " & vbLf strMsg = strMsg & " The progress indicator will vanish" & vbLf strMsg = strMsg & " when the exportation is completed." & vbLf labHelp1.Caption = strMsg End Sub Sub ProgressStyle1(Percent As Single, ShowValue As Boolean) ' ' Progress Style 1 ' Label Over Label ' Const PAD = " " If ShowValue Then labPg1v.Caption = PAD & Format(Percent, "0%") labPg1va.Caption = labPg1v.Caption labPg1va.Width = labPg1.Width End If labPg1.Width = Int(labPg1.Tag * Percent) End Sub Sub chkPg1Value_Click() labPg1v.Visible = chkPg1Value.Value labPg1va.Visible = chkPg1Value.Value Call run_the_progress_meter 'added by susan End Sub Sub UserForm_Initialize() ' ProgressBar1 labPg1.Tag = labPg1.Width labPg1.Width = 0 labPg1v.Caption = "" labPg1va.Caption = "" Call LoadHelp End Sub Sub run_the_progress_meter() Application.Cursor = xlWait Call DemoProgress1 Application.Cursor = xlDefault End Sub xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
pls help ammend progress bar code
cool, thanks!
all you changed was add the userform_activate sub instead of the checkbox_click sub, right? now that i don't need that checkbox, could i delete it (or at least make it invisible?) thanks a lot for your help. susan Bob Phillips wrote: Option Explicit Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Const PI = 3.14159265358979 Sub DemoProgress1() ' ' Progress Bar Dim intIndex As Integer Dim sngPercent As Single Dim intMax As Integer intMax = 100 For intIndex = 1 To intMax sngPercent = intIndex / intMax ProgressStyle1 sngPercent, True DoEvents '------------------------ ' Your code would go here '------------------------ Sleep 100 Next Unload Me 'added by susan End Sub Sub LoadHelp() Dim strMsg As String ' Progress Bar strMsg = " " & vbLf strMsg = " Please wait while the information is exported." & vbLf strMsg = strMsg & " " & vbLf strMsg = strMsg & " The progress indicator will vanish" & vbLf strMsg = strMsg & " when the exportation is completed." & vbLf labHelp1.Caption = strMsg End Sub Sub ProgressStyle1(Percent As Single, ShowValue As Boolean) ' ' Progress Style 1 ' Label Over Label ' Const PAD = " " If ShowValue Then labPg1v.Caption = PAD & Format(Percent, "0%") labPg1va.Caption = labPg1v.Caption labPg1va.Width = labPg1.Width End If labPg1.Width = Int(labPg1.Tag * Percent) End Sub Private Sub Userform_Activate() labPg1v.Visible = True labPg1va.Visible = True Call run_the_progress_meter 'added by susan End Sub Private Sub UserForm_Initialize() ' ProgressBar1 labPg1.Tag = labPg1.Width labPg1.Width = 0 labPg1v.Caption = "" labPg1va.Caption = "" Call LoadHelp End Sub Sub run_the_progress_meter() Application.Cursor = xlWait Call DemoProgress1 Application.Cursor = xlDefault End Sub -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Susan" wrote in message oups.com... i'm trying to make this run automatically - it was originally part of a demo written by Andy Pope where the userform was already showing & you had to push the button to start it. the form will show, but nothing happens until you click the checkbox to show the % values. then it runs. i haven't changed any of the code except to add some "call" commands to try to make it work, and removed the "private" from some of the subs (which i can replace if necessary as i have the original code in another workbook). without having the workbook & userform i know it's trickier, but maybe somebody sees something i need to do........... thanks in advance! susan xxxxxxxxxxxxxxxxxxxxxxxxx Option Explicit Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Const PI = 3.14159265358979 Sub DemoProgress1() ' ' Progress Bar Dim intIndex As Integer Dim sngPercent As Single Dim intMax As Integer intMax = 100 For intIndex = 1 To intMax sngPercent = intIndex / intMax ProgressStyle1 sngPercent, chkPg1Value.Value DoEvents '------------------------ ' Your code would go here '------------------------ Sleep 100 Next Unload Me 'added by susan End Sub Sub LoadHelp() Dim strMsg As String ' Progress Bar strMsg = " " & vbLf strMsg = " Please wait while the information is exported." & vbLf strMsg = strMsg & " " & vbLf strMsg = strMsg & " The progress indicator will vanish" & vbLf strMsg = strMsg & " when the exportation is completed." & vbLf labHelp1.Caption = strMsg End Sub Sub ProgressStyle1(Percent As Single, ShowValue As Boolean) ' ' Progress Style 1 ' Label Over Label ' Const PAD = " " If ShowValue Then labPg1v.Caption = PAD & Format(Percent, "0%") labPg1va.Caption = labPg1v.Caption labPg1va.Width = labPg1.Width End If labPg1.Width = Int(labPg1.Tag * Percent) End Sub Sub chkPg1Value_Click() labPg1v.Visible = chkPg1Value.Value labPg1va.Visible = chkPg1Value.Value Call run_the_progress_meter 'added by susan End Sub Sub UserForm_Initialize() ' ProgressBar1 labPg1.Tag = labPg1.Width labPg1.Width = 0 labPg1v.Caption = "" labPg1va.Caption = "" Call LoadHelp End Sub Sub run_the_progress_meter() Application.Cursor = xlWait Call DemoProgress1 Application.Cursor = xlDefault End Sub xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
pls help ammend progress bar code
ok, i didn't end up deleting that checkbox
because i needed to make it a boolean check..... but i'm having a little problem here...... xxxxxxxxxxxxxxxxxxxxxxxxxxx Sub DemoProgress1() ' ' Progress Bar Dim intIndex As Integer Dim sngPercent As Single Dim intMax As Integer Dim chkWkshtCode As Boolean intMax = 50 For intIndex = 1 To intMax sngPercent = intIndex / intMax ProgressStyle1 sngPercent, True DoEvents '------------------------ ' My code starts here '------------------------ If Me.chkWkshtCode.Value = False Then Call Main End If '------------------------ ' End of my code '------------------------ Sleep 50 Next Unload Me End Sub xxxxxxxxxxxxxxxxxxxxxxx i had to put that boolean check in, otherwise everytime the code cycled through it tried to open a 2nd workbook again, that was already opened. the boolean check works fine. the problem is that the progress meter goes to 2% & then sits there (only for two to three seconds) while the Main() sub does it's thing, then the progress meter continues on it's merry way & works fine. obviously, i have TOLD it to do this, even though that's not what i want. how do i get the progress meter to run concurrently with main()?? thanks again. i'll keep working on it! susan Bob Phillips wrote: Option Explicit Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Const PI = 3.14159265358979 Sub DemoProgress1() ' ' Progress Bar Dim intIndex As Integer Dim sngPercent As Single Dim intMax As Integer intMax = 100 For intIndex = 1 To intMax sngPercent = intIndex / intMax ProgressStyle1 sngPercent, True DoEvents '------------------------ ' Your code would go here '------------------------ Sleep 100 Next Unload Me 'added by susan End Sub Sub LoadHelp() Dim strMsg As String ' Progress Bar strMsg = " " & vbLf strMsg = " Please wait while the information is exported." & vbLf strMsg = strMsg & " " & vbLf strMsg = strMsg & " The progress indicator will vanish" & vbLf strMsg = strMsg & " when the exportation is completed." & vbLf labHelp1.Caption = strMsg End Sub Sub ProgressStyle1(Percent As Single, ShowValue As Boolean) ' ' Progress Style 1 ' Label Over Label ' Const PAD = " " If ShowValue Then labPg1v.Caption = PAD & Format(Percent, "0%") labPg1va.Caption = labPg1v.Caption labPg1va.Width = labPg1.Width End If labPg1.Width = Int(labPg1.Tag * Percent) End Sub Private Sub Userform_Activate() labPg1v.Visible = True labPg1va.Visible = True Call run_the_progress_meter 'added by susan End Sub Private Sub UserForm_Initialize() ' ProgressBar1 labPg1.Tag = labPg1.Width labPg1.Width = 0 labPg1v.Caption = "" labPg1va.Caption = "" Call LoadHelp End Sub Sub run_the_progress_meter() Application.Cursor = xlWait Call DemoProgress1 Application.Cursor = xlDefault End Sub -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Susan" wrote in message oups.com... i'm trying to make this run automatically - it was originally part of a demo written by Andy Pope where the userform was already showing & you had to push the button to start it. the form will show, but nothing happens until you click the checkbox to show the % values. then it runs. i haven't changed any of the code except to add some "call" commands to try to make it work, and removed the "private" from some of the subs (which i can replace if necessary as i have the original code in another workbook). without having the workbook & userform i know it's trickier, but maybe somebody sees something i need to do........... thanks in advance! susan xxxxxxxxxxxxxxxxxxxxxxxxx Option Explicit Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Const PI = 3.14159265358979 Sub DemoProgress1() ' ' Progress Bar Dim intIndex As Integer Dim sngPercent As Single Dim intMax As Integer intMax = 100 For intIndex = 1 To intMax sngPercent = intIndex / intMax ProgressStyle1 sngPercent, chkPg1Value.Value DoEvents '------------------------ ' Your code would go here '------------------------ Sleep 100 Next Unload Me 'added by susan End Sub Sub LoadHelp() Dim strMsg As String ' Progress Bar strMsg = " " & vbLf strMsg = " Please wait while the information is exported." & vbLf strMsg = strMsg & " " & vbLf strMsg = strMsg & " The progress indicator will vanish" & vbLf strMsg = strMsg & " when the exportation is completed." & vbLf labHelp1.Caption = strMsg End Sub Sub ProgressStyle1(Percent As Single, ShowValue As Boolean) ' ' Progress Style 1 ' Label Over Label ' Const PAD = " " If ShowValue Then labPg1v.Caption = PAD & Format(Percent, "0%") labPg1va.Caption = labPg1v.Caption labPg1va.Width = labPg1.Width End If labPg1.Width = Int(labPg1.Tag * Percent) End Sub Sub chkPg1Value_Click() labPg1v.Visible = chkPg1Value.Value labPg1va.Visible = chkPg1Value.Value Call run_the_progress_meter 'added by susan End Sub Sub UserForm_Initialize() ' ProgressBar1 labPg1.Tag = labPg1.Width labPg1.Width = 0 labPg1v.Caption = "" labPg1va.Caption = "" Call LoadHelp End Sub Sub run_the_progress_meter() Application.Cursor = xlWait Call DemoProgress1 Application.Cursor = xlDefault End Sub xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
pls help ammend progress bar code
I don't think you will, the workbook open doesn't allow you to issue an
update command to the progress bar until it finishes. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Susan" wrote in message oups.com... ok, i didn't end up deleting that checkbox because i needed to make it a boolean check..... but i'm having a little problem here...... xxxxxxxxxxxxxxxxxxxxxxxxxxx Sub DemoProgress1() ' ' Progress Bar Dim intIndex As Integer Dim sngPercent As Single Dim intMax As Integer Dim chkWkshtCode As Boolean intMax = 50 For intIndex = 1 To intMax sngPercent = intIndex / intMax ProgressStyle1 sngPercent, True DoEvents '------------------------ ' My code starts here '------------------------ If Me.chkWkshtCode.Value = False Then Call Main End If '------------------------ ' End of my code '------------------------ Sleep 50 Next Unload Me End Sub xxxxxxxxxxxxxxxxxxxxxxx i had to put that boolean check in, otherwise everytime the code cycled through it tried to open a 2nd workbook again, that was already opened. the boolean check works fine. the problem is that the progress meter goes to 2% & then sits there (only for two to three seconds) while the Main() sub does it's thing, then the progress meter continues on it's merry way & works fine. obviously, i have TOLD it to do this, even though that's not what i want. how do i get the progress meter to run concurrently with main()?? thanks again. i'll keep working on it! susan Bob Phillips wrote: Option Explicit Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Const PI = 3.14159265358979 Sub DemoProgress1() ' ' Progress Bar Dim intIndex As Integer Dim sngPercent As Single Dim intMax As Integer intMax = 100 For intIndex = 1 To intMax sngPercent = intIndex / intMax ProgressStyle1 sngPercent, True DoEvents '------------------------ ' Your code would go here '------------------------ Sleep 100 Next Unload Me 'added by susan End Sub Sub LoadHelp() Dim strMsg As String ' Progress Bar strMsg = " " & vbLf strMsg = " Please wait while the information is exported." & vbLf strMsg = strMsg & " " & vbLf strMsg = strMsg & " The progress indicator will vanish" & vbLf strMsg = strMsg & " when the exportation is completed." & vbLf labHelp1.Caption = strMsg End Sub Sub ProgressStyle1(Percent As Single, ShowValue As Boolean) ' ' Progress Style 1 ' Label Over Label ' Const PAD = " " If ShowValue Then labPg1v.Caption = PAD & Format(Percent, "0%") labPg1va.Caption = labPg1v.Caption labPg1va.Width = labPg1.Width End If labPg1.Width = Int(labPg1.Tag * Percent) End Sub Private Sub Userform_Activate() labPg1v.Visible = True labPg1va.Visible = True Call run_the_progress_meter 'added by susan End Sub Private Sub UserForm_Initialize() ' ProgressBar1 labPg1.Tag = labPg1.Width labPg1.Width = 0 labPg1v.Caption = "" labPg1va.Caption = "" Call LoadHelp End Sub Sub run_the_progress_meter() Application.Cursor = xlWait Call DemoProgress1 Application.Cursor = xlDefault End Sub -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Susan" wrote in message oups.com... i'm trying to make this run automatically - it was originally part of a demo written by Andy Pope where the userform was already showing & you had to push the button to start it. the form will show, but nothing happens until you click the checkbox to show the % values. then it runs. i haven't changed any of the code except to add some "call" commands to try to make it work, and removed the "private" from some of the subs (which i can replace if necessary as i have the original code in another workbook). without having the workbook & userform i know it's trickier, but maybe somebody sees something i need to do........... thanks in advance! susan xxxxxxxxxxxxxxxxxxxxxxxxx Option Explicit Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Const PI = 3.14159265358979 Sub DemoProgress1() ' ' Progress Bar Dim intIndex As Integer Dim sngPercent As Single Dim intMax As Integer intMax = 100 For intIndex = 1 To intMax sngPercent = intIndex / intMax ProgressStyle1 sngPercent, chkPg1Value.Value DoEvents '------------------------ ' Your code would go here '------------------------ Sleep 100 Next Unload Me 'added by susan End Sub Sub LoadHelp() Dim strMsg As String ' Progress Bar strMsg = " " & vbLf strMsg = " Please wait while the information is exported." & vbLf strMsg = strMsg & " " & vbLf strMsg = strMsg & " The progress indicator will vanish" & vbLf strMsg = strMsg & " when the exportation is completed." & vbLf labHelp1.Caption = strMsg End Sub Sub ProgressStyle1(Percent As Single, ShowValue As Boolean) ' ' Progress Style 1 ' Label Over Label ' Const PAD = " " If ShowValue Then labPg1v.Caption = PAD & Format(Percent, "0%") labPg1va.Caption = labPg1v.Caption labPg1va.Width = labPg1.Width End If labPg1.Width = Int(labPg1.Tag * Percent) End Sub Sub chkPg1Value_Click() labPg1v.Visible = chkPg1Value.Value labPg1va.Visible = chkPg1Value.Value Call run_the_progress_meter 'added by susan End Sub Sub UserForm_Initialize() ' ProgressBar1 labPg1.Tag = labPg1.Width labPg1.Width = 0 labPg1v.Caption = "" labPg1va.Caption = "" Call LoadHelp End Sub Sub run_the_progress_meter() Application.Cursor = xlWait Call DemoProgress1 Application.Cursor = xlDefault End Sub xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Progress bar freeze while running code | Excel Programming | |||
need to ammend a formula | Excel Worksheet Functions | |||
Add comments via a function - please could you ammend my code/ pseudocode to work | Excel Programming | |||
Code for a progress dialog?? | Excel Programming | |||
VBA code to make a progress tool bar work | Excel Programming |