Show Please Wait Message
Jan 11, 2009What is the simplest way to display a "Please Wait.." message during the execution of a macro.
Tried the WshShell.Popup but did not work.
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.
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..."
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?
I am new to excel vba. I want to show a pop-up message when user tries to save the workbook keeping cell(1,3) blank.
View 1 Replies View Relatedwe are having a excel sheet which has data in it.
ex:
In sheet3:
D |F
Columns: Logic |Field
Rows: market |business
proposed |consumer
In sheet2:
A |B
List of sheet | Field
sheet3 |business
sheet4 |other
In Sheet1:
A |B
List of sheet |Field
sheet3 |consumer
When a user points to the particular cell(f2,f3) in column F of sheet3, it should display a message weather the data in particular cell is in which sheet( Sheet1 or sheet2 or not specified) and hyperlink must be displayed. when we click on the hyperlink it should go to the data particular row of the sheet.(Sheet1 or sheet2)..
I need to refer the LAST ROW OF COLUMN "D" to appear in the message box for the below code along with " Receipt number" which is in Sheet2.
Sub saveit()
With Sheets(2)
r = .Range("B65536").End(xlUp).Row + 1
InvN = Cells(15, 4).Text
If Range("c18") = "" Or Range("c20") = "" Or Range("c20") = "" Or Range("c24") = "" Then
MsgBox "Please fill all required fields", vbCritical, " Missing data"
Exit Sub
End If...............................
This seems like a very simple question but I can't find the answer that I'm looking for. I have a spreadsheet with a drop-down ( validation) list. Based on what the person selects, I then would like to have a message box to come that would direct them that certain cells then need to be filled in.
View 4 Replies View RelatedIs it possible to display a dialog box or msgbox that doesnt have an OK button ?
i.e I want a message that comes up on the screen that says "Links Updating...Please Wait" which then automatically changes to "Links Sucessfully Updated" on completion...I dont want the macro to be interrupted by the msgbox/dialog....
I have a spreadsheet where I have input all of my technicians service calls and installations.
In Column C Row 5 and up to the max I assume because I'm not sure how many rows I would use in a year, I have an estimated completion date.
In Column D and the same Rows as above I will input the Actual Completion date.
What I want is if the current date is past the estimated completion date (Column C) and there is no date entered in the Actual Completion date (Column D) I would like a MsgBox to pop up saying "Please enter a new completion date on row _"
"_" would be which ever row the estimated completion date has expired without an actual completion date being entered.
What i am trying to do:
1) check whether any value in column J, rows 7-18 is changed,
2) check whether the value is above the value in col H, respective row multiplied by 1,25,
3) if point 3 true, clear the contents of cells E24, F24, and show up a messagebox,
4) run another macro by pressing button in cell C24 in order to fill the E24 and F24 cells again.
What happens: Points 1-3 work perfectly well, but then i have a problem in point 4 - the messagebox of point 3 appears again and the work of macro of point 4 interrupts showing an "Application-defined or object-defined error".
The file with the code is attached : Example.xlsm
I am close to finishing making up an Excel tool that takes data input from the user, does some analysis, accepts further input then prepares a report. My coding skills are not what you would call high level, so my code could probably be more efficient, but it works (well, except for this issue).
The tool opens a main multipage userform for data entry, then allows the user to view the data in the spreadsheet tables with the ability to call separate userforms to modify, delete or add records. The user then calls another userform to do some calculations and enter the appropriate results from a separate piece of software, then presses a button to prepare a report. All userforms are called from buttons in a separate userform (I'm not proficient enough and haven't had enough time to create a new ribbon in excel). At various times, messageboxes are used to convey information or prompt the user to do something - these are all simple ones with either only OK button, or OK and cancel buttons.
My problem is that when userforms are called, they sometimes appear but are totally blank (white) - see image.
blank_form.jpg
Clicking anywhere on the screen or pressing any key will bring them up properly and they seem to function normally thereafter.
In addition, the messageboxes sometimes do not appear - pressing any key will make them do so. Other than this they function normally.
The frustrating thing is that these problems occur inconsistently. I can add or change some code and it stops happening, then after testing (entering data and using the tool) starts happening again. Sometimes it just stops happening with no apparent reason, but starts again later after fixing some code or just entering some data. Not all userforms are affected at any one time and not all messageboxes have the problem at any one time.
The problem seems to occur in those subroutines where I access or manipulate data from other sheets before showing the userform or message box (but in most instances, I need to do this manipulation in order to present the correct information in the userform).
Some thought processes I have had, and unsuccessfully tried to fix the problem with (some coming from various web forums):
I thought I may have done "Application.ScreenUpdating = False" without resetting it to true, so I commented out all the "Application.ScreenUpdating = False" statements - problem stopped for a bit then started again.
Some of the userforms were modal, and even though they were not showing were still loaded so thought that this may be impacting on other userforms/message boxes, so changed all userforms to non modal - still had the problem when I opened the tool again.
Tried using "RePaint" and "DoEvents" at various points in the code after opening a userform or message box - no change.
I have tried exporting a form and its code, removing from the tool, then re-importing it. No success.
Tried the tool on another computer and the same problem occurred.
How to show via a message box the difference between rows 2 and 3 on a worksheet (row 1 is headers).
In the message box per the attached workbook, i should only see something like the following....
Drawing Rev was A is now B
Planner was Joe is now Fred
The attached example is abbreviated, the actual data will span multiple columns (approx. 30 - 40)
In a range of cells (e.g. B26-B40) I have names (first name and second name or first initial and second name). This data is carried from sheet 1 to the second sheet via formula. Some cells may be blank as well.
A VB code to display a message when the user changes the name in any of these cells with data or adds a new name to a cell that is blank, in sheet2
I have a userform with a combobox (cmbSearch) , a textbox (textbox1) and a listbox (listbox1)....and a commandbutton (cmbFind). This userform is used for searching records in my worksheet....where user selects a "Search By" category from the combobox and then enters relevent text search criteria in the textbox....and all the search results are shown in the listbox. This part works fine untill someone tries to search a "non-existent" record. e.g. a customer name thats not in the database and it gives an error. I would incorporate in my code to have a Message box pop up on these type of searches saying "textbox1.value Not Found!" insetad of error message.
Private Sub cmbFind_Click()
Set c = Range("a65536").End(xlUp).Offset(1, 0)
'write userform entries to database
Dim DataSH As Worksheet
Set DataSH = Sheets("ComplaintData")
With DataSH
.Range("L1").Value = cmbSearch.Value
.Range("L2").Value = TextBox1.Text
.Range("N1"). CurrentRegion.Clear
.Range("A1").CurrentRegion. AdvancedFilter Action:=xlFilterCopy, criteriarange:=.Range("L1:L2"), copytorange:=.Range("N1")
End With
lstSearch.RowSource = vbNullString
lstSearch.RowSource = "outdata"
End Sub
I need a macro where user choose a value from validation, automatically lock the other cells and should show an error message.
Here is what I am looking for. User will choose Yes or no from cell H15 validation. If user choose "NO" from the validation then all other cells has to be locked in the workbook automatically except Cell H15 and a msg box needs to appear stating: "Select Yes from the validation".
I'm trying to check and prompt a message box if there is a empty cells found in the pivot table.
If Activesheet.PivotTables("PivotTable1").NullString = "" Then
MsgBox "No Match Data Found"
End If
I have try out the code caption above but not the result as I want.
Below is a macro I recorded to help copy 'Paste Special - values only' data between two spreadsheets that have different colour and conditional formatting in cells.
However, if this is run and there is no data to paste, it throws up the End and Debug error.
Any chance that someone knows that if there is no data sitting on the Clipboard, then a msg box would appear saying "Please select the data you require to be copied" in the message box.
Sub data_input()
ActiveSheet. Unprotect
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B5:AG4804").Select
Selection.sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("B5").Select
ActiveSheet.Protect
End Sub
I have a file that the user selects and when they enter a value (in this case, a job title) into the input box, my macro looks for the value in that file. If the value is there, a msgbox pops up that lets the user know that the value was found and it then goes to that cell, displaying in the next msgbox the cell address of where the value was found. I already have the code down for this part of the macro.
My problem is what happens if the file has the same value more than once. Ideally, I'd like to display a message box that returns the addresses of both cells with the same value and then prompts the user to select one of these values as the value they are looking for. I am not sure if a msgbox or a msgbox and then an input box are most appropriate for this situation. Once the user does this, the macro continues. The rest of my macro is built on the cell where this value is, so it is crucial that I make sure there is at least one value selected. Any help is much appreciated. I have included a sample worksheet of what this situation might look like.
Here is the code I presently have for this part of my macro.
Sub GetOpenFileName()
strFind = InputBox("Please enter the job title you wish to search for:", "Search for job title in this file")
If strFind = vbNullString Then Exit Sub
With Sheets(strSheetsMainCompProfile)
If WorksheetFunction. CountIf(Range(Cells(1, 1), Cells(100, 100)), "*" & strFind & "*") = 0 Then
MsgBox strFind & " cannot be found on this sheet"
Else
I cannot figure out how to get my error handler to work, or actually, not work. It seems to work fine when there is an error, but the code still gets read even when there was not an error. Basically, I am trying to open a file, which may or may not be there. When it is not there I want a message to pop up informing the user. However, when the file is there and it opens, the error handler still gives the message box. Any ideas what I am doing wrong?
Private Sub btnOK_Click()
Application. ScreenUpdating = False
Dim LCSfile As String
LCSfile = frmSelectFile.Listbox1.Value
On Error Goto ErrHandler
Workbooks.Open Filename:=sPath & sDate & "" & LCSfile & "QUANT.CSV"
ErrHandler:
MsgBox ("File is not quantitated. Please select another file.")
Application.ScreenUpdating = True
End Sub
I have the following code to compare two columns and delete adjacent rows if 1 is greater than or equal the other...
Sub LastReceipt_GT_Confirmed()
Dim intLstRow As Integer
For intLstRow = Range("E" & Rows.Count).End(xlUp).Row To 2 Step -1
With Range("E" & intLstRow)
If .Value > .Offset(0, 1).Value Then .EntireRow.Delete
End With
Next intLstRow
For intLstRow = Range("E" & Rows.Count).End(xlUp).Row To 2 Step -1 .............
I need to check a range of cells (B4:B35) and see if any of the contents are less than a specified cell (M1) and then show a message, (the message part I can do). I have tried using For Each but I then get the message for every cell that is below the specified cell (which in theory could be all of them). I have also tried using an If Any statement but didnt work.
View 5 Replies View RelatedI use the following macro to save my worksheet. However, if I click cancel when the save box is open, then I am presented with an error. How do I get it to skip the error and show a message box instead that says "File not Saved!".
View 2 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.
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 RelatedBelow 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 RelatedI'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"))
been 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?