I've a userform style progress bar which stops updating if i use application.wait within my code like this:
UserForm1.FrameProgress.Caption = "0%"
UserForm1.Label1.Caption = "Starting Process.."
UserForm1.LabelProgress.Width = 0
UserForm1.OkButton.Visible = False
DoEvents
Application.Wait (Now() + TimeValue("0:00:01"))
I want to use a msgbox as a progress indicator for a routine in an application. I cant figure out how to do this.
View 5 Replies View RelatedIm consolidating datas from muliple excel files to single data base excel file.Am having macro and it is working fine for me.But when am loading data to data base file the excel files are vibrating.To avoid this issue i want to Set the progress bar which should indicate the progress of the data loading.
View 12 Replies View RelatedHow do I pause my macro until the user selects a chart in the worksheet? Or Keep prompting a message to the user till he selects the chart and when a chart is selected run the macro.
Step 1: Check if a chart is Selected
Step 2: If yes, run the macro
Step 3: If no, prompt the user with a msgbox of Retry/Cancel.
Step 4: If the user selects Retry, wait till he makes a selection.
Step 5: If the selection is a chart, run the macro. If the selection is not a chart prompt the same msgbox again and keep looping it till he selects a chart.
Sub test5()
Dim chtSelected As Chart
Dim UserResponse As Integer
On Error Resume Next
Set chtSelected = ActiveChart
[Code] .....
I have a very long code with multiple functions and operations i.e. it calculate many fields. The normal running time varies from 2 minutes to 30 minutes depending on the data size.
Can any one tell me a way so that I can put a kind of status bar to show the progress or estimated time left? Basically, during operation it looks like excel is hanged and not responding but infact its not.
What is the simplest way to display a "Please Wait.." message during the execution of a macro.
Tried the WshShell.Popup but did not work.
how do I code in vba for it to wait until the user has checked the file that has been made make a few amendments and then when done one would click OK or resume for the program to carry on with the rest of the code.
View 2 Replies View RelatedIm copying and pasting data from one workbook to another but when I want to close the source workbook, it comes up with this message that I have much data and if I want to keep this in a clipboard. I thought I could disable this with Application.DisplayAlerts = False but when I do this, Excel freezes. Im I doing something wrong. How can I supress this window?
Public path As String
Sub Get_data()
path = "\Nlchoosa.nlOPS_Processes$OPS_ProcessesReports Sector performance"
Workbooks.Open Filename:=path & "ReportsSector Performance Reporting week.xls"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.copy
Windows("Sector Performance report Week.xls").Activate
ActiveSheet.Paste
Application.DisplayClipboardWindow = False
Windows("Sector Performance Reporting week.xls").Activate
Application.DisplayAlerts = False
End Sub
Below is the code I currently have implemented. The first line of code creates a .csv file. The third line of code opens that file. I couldn't put the third line right after the first because it would try to open the file before it was finished being created. Hence, the second line which waits for 5 seconds. Is there a way, either in the CMD or VBA, that I can have it wait until the first line of code has completed?
VB:
Shell "cmd.exe /c M:" & "&& cd DesktopExcel Project" & Command
Application.Wait Time + TimeSerial(0, 0, 5)
Workbooks.Open("M:DesktopExcel ProjectInfo.csv")
If I run this in the editor sometimes it works and sometimes it does not.
If I run this in editor with F8, stepping through the code, it does work.
I can only guess that enough time is passing during the F8 presses that the page can load. But is that not the purpose of adding the wait code?
[Code] ....
Is it possible to insert a wait command in milliseconds- minimum I have seen is a second?
View 1 Replies View Relatedbeen checking out 1/2 a second wait instead of a 1 sec wait
and would like to know it you can do this
timer thing
Dim x As Single
x = Timer
While Timer - x < 0.5
Wend
more than once
it works the first time and then it sort of skips it
or is there ANOTHER way of having a delay of less than a second?
I'm using a pre-made spreadsheet from my stock broker (Interactive Brokers) that retrieves and displays real-time quotes, and allows one to retrieve historical stock data, among other things. I've created a little macro within it to try and automate some common tasks I do everyday - basically I want to retrieve a year of daily stock quotes for "stock 1" from IB's servers, then have the macro wait for the retrieval to be done and written to the spreadsheet (takes anywhere between 10 and 30 secs). After that's all done, then I want to do the same for "stock 2". Then when that's done, the macro proceeds to go ahead and do some calcs on stock1 and stock2. The problem is after my stock1 data request, the macro just keep on trucking through to the next commands while stock1's data retrival is still going on.
so things are getting all balled up. How can I get my macro to wait until stock1's data retrieval is all done?
My question is if it's possible to change the code and make it work faster. When I change the ComboBox I wait about 1-3 seconds for about 40 items in the Worksheet. I still didn't test it with more items. Here's the
Private Sub ComboBox1P2_Change()
If ComboBox1P2.ListIndex = 0 Then
ComboBox2P2.Style = fmStyleDropDownCombo
ComboBox2P2.Value = sign
ComboBox2P2.Enabled = False
ComboBox3P2.Clear
ComboBox3P2.Enabled = False
ComboBox1P2.SetFocus
ElseIf ComboBox1P2.ListIndex 0 And ComboBox1P2.ListIndex -1 Then
ComboBox2P2.Style = fmStyleDropDownList
ComboBox2P2.Enabled = True...........
I have a workbook with alot of results that employees will need to wait for results to show.
While it Calculates.. can we get a cell to show "Please Wait - Searching" so they know its actually doing something for them?
When clicking the button on my userform, it goes through a quite complex process of changing the views in several pivot tables that are linked to an "report" sheet, which has all the figures the user needs in a neat format.
Changing these pivot tables takes up to a minute, so I wanted a userform, called "frmwait", to pop when clicking the command button. It would say "Generating Your Report - Please Wait". At the same time, the initial userform, which is called "frmroutedashboard", would be hidden.
This does not happen - the "frmwait" userform shows, however the "frmroutedashboard" does not hide.
Is this because the code I composed uses the values on the "frmroutedashboard" to generate the view, and it cannot hide until the report is completed? If so, there must be a way around this. Anyone know how?
The initial code so far looks like this:
frmroutedashboard.hide
Application.Cursor = xlWait
Application. ScreenUpdating = False
Application.DisplayStatusBar = True
Application.StatusBar = "Updating Route Dashboard..."
I thought if it looked like this, it would be sufficient. Unfortunately, the file is too large to attach, so I hope the code suffices.
frmroutedashboard.hide
frmwait.show
Application.Cursor = xlWait
Application.ScreenUpdating = False
Application.DisplayStatusBar = True
Application.StatusBar = "Updating Route Dashboard..."
Situation:
At two places in the middle of my code I need to specify a particular variable; usually this is done by some autodection-method, but whenever this fails a userform gets loaded and the user needs to do some clicks which finally specifies that variable.
Problem:
So far my code does NOT wait for the userform (and the user-input), but continues running until it fails, cause that particular variable (see above) had not been initialised with the correct value for this run.
My Question:
How can I force the code to wait until the userform has been closed? Or what other way do you usually solve that problem when wanting to use a userform in mid-code? Below you find the place where I load the User form 'UsrFormSpecifyFormat' ....
when I run a macro, it takes me to a different sheet, clicks on a cell, and then goes to data--> form to automatically generate a form so I can make a new entry. I want the macro to wait until I have completed the form, and on completion to do something else. I'd prefer it if I could indicate completion of the firm by just a keystroke, but a mouse click will do as well.
View 2 Replies View RelatedHow can I put a VBA script to wait for 30 seconds, then close the Excel file (w/o saving it) which its name is stored in CSV_file_name variable, then re-open it?
I am using Excel 2007.
I have some pivot tables that have the data source in another workbook. I didn't create any external data connections. When I created the first pivot table, I just selected the range from that other workbook. Most of the other pivot tables were copied and modified accordingly, so they should have the same pivot cache.
I have created a macro that basically refreshes all data, using the Workbook.RefreshAll command, then copy/pastes values and then deletes the pivots so that I can send it via e-mail without problems. The macro seems to work fine.
The QUESTION relates to the fact that I am afraid VBA won't wait for the refresh to complete before starting to copy/paste values.
The PivotCache.BackgroundQuery = False solution doesn't work. I get 10004 error. Also, there are no data connections visible in the Data > Connections. I guess that, because of this, "Properties" is greyed out in the Data tab and also "Connection Properties" under Pivot Table Tools > Options > Change Source Data is greyed out. This way, I cannot uncheck the "Enable background refresh" box.
I could just tick the "Refresh when opening the file" in Pivot Table options, but if I open the file without first opening the workbook with the raw data, refresh takes very long.
I have tried to measure time difference between the RefreshAll command and a simple Range("C3").Select command. There is a time difference, but I'm still not 100% sure that VBA waits for refresh to complete before continuing.
VB:
Do Until Application.CalculationState = xlDone
VB:
[COLOR=#333333][COLOR=#333333]DoEvents[/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333]Loop[/COLOR][/COLOR][COLOR=#333333][COLOR=#333333]
[/COLOR][/COLOR]
The macro stops in the last row - it isn't able to select a cell in another sheet than "Menu" (I tested it different variations).
View 9 Replies View RelatedMy code below works great all the way to the end. When the last message box comes up, If the user chooses Yes. I want them to be directed to the sheet "C123". But currently it just stays on the sheet that I named "Home".
What am I missing here?
Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Log")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'check for a Reason Code
If Trim(Me.ComboBox2.Value) = "" Then
Me.ComboBox2.SetFocus
MsgBox "Please enter a reason code and ensure that date is correct!"
Exit Sub
End If
I have a macro where I manually select a cell then the macro kicks in to copy and paste the contents into the Find function. From there it goes to another work sheet, clicks on a cell in column one and searches for matching cell contents. Then If false it manually goes back to the original worksheet/cell and then I input a N in the left adjacent cell. If true I enter a Y. Right now I can only run this for the specific cell the macro was recorded for. I would like to expand this.
What I would like to do create a loop in the macro that waits for my cell choice input then continues with the with the Find function. If false the macro should just loop back to the original worksheet/cell and wait for input, ie the next cell selected. However, if true a worksheet/cell is selected, a Y is input and it loops back to the original cell and waits for input.
I want to allow users to place files (.jpg, .tif, .pdf, .pps etc) into a network folder and then have my program open each file and display it for 30 seconds then close the file and the application before opening the next file.
1. Set up a loop to get a directory listing of the folder and write the listing to a text file.
2. Open up the text file and get the next filename in it
3. Open the file in the associated application
4. Wait for 30 seconds (or some period of time) and then close the file
5. Repeat steps 2-4
DoIt = 1
While DoIt = 1
Open "C:TempList.txt" for output as #1
Print #1, Files In Folder
Close #1
Open "C:TempList.txt" for input as #2
while not eof(2)
Line input #2, MyFile
Display MyFile on screen
Wait for 30 seconds
Close MyFile and MyApplication that opened it
wend
Close #2
I can get the directory listing just fine with no problems
I can open the files in the associated application just fine with no problems.
with closing the application after 30 seconds or some period of time.
I need some code that will allow me to easily send it a filename and it will know how to close the file and the application that opened it.
I have a spread sheet which has a number of protected cells so that they user can tab only on required cells to enter data. At times some of the rows are hidden however pressing "TAB" will continue to tab through on the cells within the Hidden Rows. Is it possible to only TAB through Cells which are visible?
View 1 Replies View Relatedi ran some tests and it was working good only doing folders with ~100 files. i tried running the macro on all the files (~70,000), but I ran into problems with the computer going into standby while I was away. I tried running a batch of 3000 files and it completed, but only got up to around ~400 out of ~3000 records. Maybe, it stops cause of code related inefficiencies?
Here's the code:
macro_forexcelforum.txt
I have 3 Work books - one (WB1) checks its version number against an online record and if they dont match it opens WB2 which then proceeds to download the updated version copy information from WB1 to its self which it then pastes into WB3
WB1 then gets deleted and WB3 Renamed to the old name of WB1
the issue i have is when i open WB2 using the button that also checks for updates on WB1.
WB2's code seems to just stop when it gets to the point of updating WB3 no error messages or nothing it just stops!
NewMasters(2).xls = WB1
Updater.xls = WB2
NewVersion.xls = WB3
I have this macro which sits in the workbook module:
Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldRng As Range
On Error Resume Next
If Application.CutCopyMode = xlCopy Or Application.CutCopyMode = xlCut Then
OldRng.EntireColumn.FormatConditions(1).Delete
OldRng.EntireRow.FormatConditions(1).Delete
[Code] .......
It's used to highlight the selected/active row.
But, as soon as I run another macro, it simply stops working entirely and I'm left with a row permanently highlighted until you manually remove the conditional formatting. All formats and cell colours controlled in the other macro stop working as well. This is the other (edited for publishing) macro, which is used to send an email, and is found in a module:
Code:
Sub Send_Mail()
'this sends an email that sends a text message
Dim OutApp As Object
Dim OutMail As Object
Dim MyButton As String
Dim strTo As String
[Code] .......
I didn't write the first macro so I'm not sure where the problem is? I'm guessing there is something in the code that is stopping it from working (i.e. running conditional formats) as soon as another macro is run? Is that was is happening?
I have a macro stored a module at Personel workbook and i am using personel workbook as a hidden one. when i assign a shortcut below macro it is not working but whrn i am press F5 at VBA Editor it is working.
Here is the code:
Code:
Sub Transferdata()
On Error GoTo err1:
Application.DisplayAlerts = False
[Code]....
I have a problem with the following code
Dim strProcLine As String
With ActiveWorkbook.VBProject.VBComponents(ActiveChart.CodeName).CodeModule
' MsgBox ok
' adaugat procedura goala
.CreateEventProc "Calculate", "Chart"
'MsgBox ok
strProcLine = "Format_Chart"
' MsgBox ok
.InsertLines .ProcBodyLine("Chart_Calculate", 0) + 1, strProcLine
' MsgBox ok
End With
' MsgBox ok
This is part of a larger macro, wich makes 2 pivottables and for each PT a chart, and for each chart i create an even procedure (Chart_Activate) wich calls a procedure to format the chart. Tha macro is alocated to the click even on a button in the sheet where i get the information from.
If i run the macro(click the button) with Microsoft Visual Basic Editor opened all goes ok. But if i close Microsoft Visual Basic Editor and then run the macro it stops right before .CreateEventProc "Calculate", "Chart" of the first chart , and i can't understand why. No error mesages delivered, nothing.