Adding Status bar for Do.. Loop..

Topic

New Reply

Anyone know of a method to add a status bar to give the user progress on a do.. loop? the context is as follows:

‘*************CODE START**************
Private Sub btnmaximize_click()
Userform.Show
Do
Call userform.MoveNextButton_Click
‘find out how to add delay here to effect movenextclick.
Loop While userform.counter.Value < Range("AC" & 1).Value
End Sub

****************CODE END****************
Now, the value of Range("AC" & 1) is a variable which increases as I 'Navigate' the form.

Any help would be appreciated!!

Thanks!

Reply | Quote

Replies

What about putting it in Excel’s status bar (or am I missing something and you’ve already dismissed that). If I’m not missing something, the code is:

Application.StatusBar=”Text”

To revert back to the normal values in the status bar, turn off your text with

Application.StatusBar=False

HTH

Jon

Reply | Quote

Drk,

Jon’s answer has prompted me to ask where you wnat the status bar. Is in in the Excel status bar? Or is it on the userform somewhere?

Reply | Quote

I had hoped to use an Excel Status bar. It all relates to this thread, which refers to a minimize button on a userform, I think the only way to do it, is to call ‘btnmovenext_click’ n times, n being the integer defined when the user clicks a ‘minimize’ button i’ve placed on the form. Because the form is being used for data entry, I have a variable ‘i’ which represents the current row. The row they left off on is captured in cell AC,1 on the worksheet, that number is the number of times ‘btnmovenext_click’ should be called. If there are 40 entries they left off on, I want them to move to the 40th row when they ‘maximize’ the form.

Thanks!

Reply | Quote

Could you add some code in the form’s initialize event to set n to whatever value is in that cell?

Reply | Quote

Sure,

dim n as integer
n = Range(“AC” & 1).value

what would that do? Another problem i’m having, the do.. loop.. will move VERY quickly, I need to delay it, any ideas?

Reply | Quote

OIC….

Hmm.. check this out.. Because the entire ‘navigation’ portion is based on the value i, Example: Range(“A” & i).value=textbox1.value, perhaps I can make i the value of cell AC,1. would that work!?? hmmm…..

Reply | Quote

Okay,
when the form initializes, i am setting i=2, to start on the second row on the worksheet. Can anyone give me an idea for a condition I can specify? I’m thinking something like:

Sub btnmini_click()
dim n as integer
n=2
Range(“AC” & 1).Value = counter.Value
frmtrack.Hide
Application.WindowState = xlMinimized
end sub

Sub btnmax_click()
if n=2 then i = Range(“AC” & i).value
else
i=2
end sub

but it’s not working.. any ideas?

Reply | Quote

Hi,
n is declared within the first sub, so loses its value when that sub ends. Try declaring it publicly and see if that works.

Reply | Quote

Nope, no go, I even tried recording ‘n’ to a cell in the worksheet, and basing an ‘if’ statement on the result, to no avail…

no matter where ‘n’ is placed, be it public or not, the value assigned is reset when the form is hidden.

Reply | Quote

Syntax, Syntax, Syntax… for the record, VBA doesn’t like the syntax of:

Range(“AC”, 1).value it has to be Range(“AC” & 1).Value

My problem is solved now, but in the interests of completing the thread, does anyone know how to add a status bar for a do.. Loop.. ?

Thanks All!

Reply | Quote

What do you mean by “status bar”? If you mean a progress bar (a bar that increases in length as the progress of the loop progresses), then my message in this thread tells you how to do it. If you mean something else, what do you mean?

Reply | Quote

Err.. Progress Bar.. and so it does!
Thanks Legare!

Reply | Quote

VBA in MSAccess has a SysCmd method that can be used to display a progress bar in the status area. I do not think that Excel has this or a similar capability. If you are wanting to display a progress bar, then you will have to do it with your own code. Just define a string variable and initilize it to null. Then in the loop add the character you want to use for your progress meter to the end of the string and assign the string to the status bar. If the bar moves to rapidly, or gets too long, then use an If statement to only add a character and display every nth time through the loop.

Reply | Quote