How To Check For User Interaction Whenever Change Detected
Mar 9, 2013
I was told that the following code would enable my macro to execute every time the user interacts with anything Excel:
Code:
' Repeatedly check for user interaction whenever a change is detected
Private Sub Worksheet_Change(ByVal Target As Range)
Call TestMacro
End Sub
It doesn't work...
To put it into context, he is my entire program:
Code:
Sub TestMacro()'
' TestMacro Macro
'
' Create string variable
Dim undoText As String
' Assign the text to string variable
[Code] ......
My being new to VBA doesn't work in the situation as my programming instincts tell me to set up an infinite loop to continually check for interaction something like the following:
Code:
while( x == 1 )
{
do MY_CODE;
if( USER_CLOSES_EXCEL )
x == 0;
} // end while
However, Excel is apparently 'Event Driven' so I'd love to know how to use this to my advantage in keeping my macro running.
View 2 Replies
ADVERTISEMENT
Jun 22, 2009
I have created a multi utility user form that user can initialize by a shortcut key. The problem if user want to see the content on sheet or want to change the active cell, he has to close the form and then do any action. This form is initialized by a command and default included in excel as a Add-in. Is there is any way to make the form floating i.e. user can manipulate the data and can see the form as well.
View 4 Replies
View Related
Nov 25, 2009
Is there a way to use MsgBox without any necessary user interaction to continue on with the macro? For example, I want to occasionally display a MsgBox for a few seconds containing relevant data and then have it disappear and the macro continue on without the user having to click the OK button, etc.
View 3 Replies
View Related
Oct 27, 2008
I have a situation where I have an msg box pop up that asks the user to identify whether or not the file they have chosen meets the standards needed to continue running the macro. Specifically, I need them to answer "Yes" if the file has a list of data in the first column, if there are no blanks in that list and if the first row of data begins on Row 4. If they answer yes, the macro continues ahead and does what it needs to. If the person selects "no," another input box pops up that asks the user to make the edits. This msg box asks the user to click "ok" when they have made those edits. I want the macro to pause after the person has selected "no" from the first msg box, so that the person can make those corrections. Then, I want the macro to resume when the user selects "OK" on the second msg box. How might I do this? I have included the piece of my code below.
strresponse2 = MsgBox("Please confirm that the file you have selected meets the following standards:" & vbNewLine & "1. The information in the first column of this file is all of the job titles or job codes associated with this profile." & vbNewLine & "2. From the first job code or title to the last, there are no blank rows in this first column of data." & vbNewLine & "3. The first job title or code appears on Row 4, Column 1." & vbNewLine & "If the file you selected meets these standards, select Yes. If the file you selected does not meet these requirements, select No.", vbYesNo, "Yes/No")
If strresponse = 6 Then
Call getjobtitle
End If
If strresponse = 7 Then
'pause macro
strresponse2 = MsgBox("Please make the necessary edits to this file. When you are done, select OK to continue generating your job profile.", vbOKOnly, "OK")
If strresponse = 0 Then
'resume macro
Call getjobtitle
End If
End If
View 5 Replies
View Related
May 22, 2014
I have a cell (B1) in the second Sheet. It contains two sentences of text, which will be spoken when entering that Sheet (by a click in the previous Sheet).
Code:
Sub ButStart_Click()
Sheets(2).Select
Range("B1").Speak
End Sub
When the Speak command is running no other user interaction can be made. As the text is quite long not all the users want to wait until it is finished. So I need the users to be able to click on the "Next" button which takes them to the next Sheet (and start speaking the text there).
View 1 Replies
View Related
Apr 18, 2012
I have a large file that takes a long time to close.
I would like to display a message that says " SAVING - PLEASE WAIT".
But I do NOT want any interaction from the user to continue so I can not use a MSGBOX.
How can I do this?
View 7 Replies
View Related
Feb 17, 2010
I am trying to check a user input to see if the user has entered the date correctly. I am using the code below but it keeps giving me a "Type Mismatch" error.
View 2 Replies
View Related
Jun 16, 2006
find attached zip file . if you open the file then you can understand the problem.
I need to change cells color and text color as per user choice in Password protected sheet : (user is useing excl2000)
Kindly open attahced file i putted the note inside the file.
View 3 Replies
View Related
May 31, 2006
I am using the following code which works fine:
'Checks user name before opening
UN = Environ("USERNAME")
If UN = "lemons" Then
Sheets("Title").Select
Range("a1").Select
Else
ActiveWorkbook.Close
End If
But when I change the code to check for more than on user as shown, I am getting a complile error.
'Checks user name before opening
UN = Environ("USERNAME")
If UN = "lemons" Then
Sheets("Title").Select
Range("a1").Select
If UN = "LEMONS" Then
Sheets("Title").Select
Range("a1").Select
Else
ActiveWorkbook.Close
End If
I need to add or a better way to check the user name
View 3 Replies
View Related
Feb 28, 2009
My problem is trivial but annoying. When I initiate my user form, one of the check boxes has a dashed border around the outside as if it was selected. No matter what I try, I cannot get the form to initialize without effecting this box. If I delete the box and recreate it, another box simply inherits this problem.
I have attached the spreadsheet, please Click the button over "A1" to see what I mean if you have the time.
View 2 Replies
View Related
Dec 6, 2006
I would like to be able to turn the following worksheet in to an inspection scheduler. It already displays job information pertaining to the job & lot number entered into cells C2 & C3 in cells F2:F14. Now, I would like to write a script that will do the following...
If lets say I click on "Groundwork", it will know that I am trying to schedule a groundwork inspection for the permit number in cell F11, and it will know it needs to be scheduled through Fairfax County's website, because it says "FFX" in cell F2. (the only two counties I really deal with online are Loudoun & Fairfax...I'm not worried about the others). So it will then automatically jump to Fairfax County's website (links to the websites are in cells E22:E26), conduct the appropriate steps to schedule an inspection through that county, then kick back some sort of confirmation that it was scheduled. The same steps apply each time for each inspection type, but each county has their own steps....
View 14 Replies
View Related
Mar 19, 2009
i am writing a program that asks the user for an input and sets it equal to a variable.
i want to look thru a column for this variable, if it is not in it, then paste it at the end of the column. if it is in already, throw up a dialog box saying its already used and exit the sub.
i dont know how to set up this kind of thing up???
vlookup in combination with an if statement?
View 6 Replies
View Related
Jun 6, 2008
I am trying to ask to the user to check if they logged out when they close the workbook but my code is not working...
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("OD&D Log-in").Select
If Range("H5") = "reconcile" Then
a = MsgBox("Do you want to Log-Out?", _
vbYesNo)
If a = vbNo Then Cancel = True
If a = vbYes Then
Sheets("OD&D Log-in").Select
Else
Workbooks("Daily OSD Log (ver5).xls").Close SaveChanges = True
End Sub
View 9 Replies
View Related
Jul 28, 2004
I have a program that opens an excel-workbook, I first check whether the file is opened by another user(open for read-write). This works fine, but I'd like to know which user has the file open with VBa code. ex.
workbooks.open ....
if open
then msgbox " Book opened by user"
end if
So it's the same as you open an excel(with your windows explorer) on a network and you get the message that the file is already opened by the user....
View 2 Replies
View Related
Feb 22, 2007
This is what i want to do: SEE ATTACHEMENT
•Write codes to pop up an InputBox to ask the user for a customer name.
•The program uses the user’s input to check whether the name is on the list. If it is, display an msgbox saying that the customer name is on the list, and the corresponding cell will be indicated in boldface and in blue. Otherwise, an msgbox will be displayed saying that the customer name is not on the list.
Dim the customer list as an array (string var. type) and Dim Found as Boolean. You will need the If-Then construction and For-Next or Do-While/Until loop too). Create a button to run and another to restore the formatting to its original style.
This is what i did so far and still having problems with it:
Option Explicit
Sub customers()
Dim Arr()
Dim R As Integer
Dim C As Integer
Dim ReturnColumn As Boolean
View 6 Replies
View Related
Aug 31, 2007
want to do something simple, and it just wont paste. not sure why. anyone care to look it over and tell me what i'm doing wrong here?
'Insert Date Information
Dim vDate As Date
Range("D2").Select
ActiveCell.FormulaR1C1 = Date
vDate = Date
'Get date from user
Dim dDate As Date
vDate2 = Application.InputBox(Prompt:="Type in the due date for the location." _
& Chr(13) & Chr(13) & "*If you want the date to default to " & Date + 5 & " then leave the field blank.", _
Title:="Due Date", Type:=1 + 2).............
View 3 Replies
View Related
Nov 9, 2008
I want to write a piece of code to go to some webpage(URL in some excel cell) and then copy all the text on the page and paste to some other cell in the same worksheet.
I know I can open IE using VBA(Excel 2003),but is it possible to get everything on the IE window into the clipboard and then paste into any of the excel cells?
View 2 Replies
View Related
Aug 27, 2012
While running one of my application, excel will be opened at the middle. I need to minimize the excel and stop interacting with it, which means even if I click the excel task bar it should not open excel. It should be always in minimized state.
I have an code which works in XP and is not working in Windows7.
Code Snippet: ( which works in XP )
ExcelObject.WindowState = xlMinimized
ExcelObject.Interactive = False
View 5 Replies
View Related
Nov 22, 2006
I need to use both of these VBA scripts in my excel project. The first is this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheets("Sheet2").Range(ActiveCell.Address).Value = 1 Then
ActiveCell.Interior.ColorIndex = 3
End If
End Sub
The second is this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lScrollRow As Long
Dim lScrollColumn As Long
Dim lTargetRow As Long
Dim lTargetColumn As Long
Application. ScreenUpdating = False
lScrollRow = ActiveWindow.ScrollRow
lScrollColumn = ActiveWindow.ScrollColumn..................
When I enter both into the script box and then try to run it, it says that there is a compile error with an "ambiguous name detected "worksheet_selectionchange"
View 4 Replies
View Related
Aug 28, 2009
Trying to design a check that makes a user insert an integer between 0 and 90. So far I can use this:
View 3 Replies
View Related
Jul 28, 2012
I want a simple VBA code that when run will check if a particular spreadsheet that exists on a Network Drive is already open or not by another user. Imagine there are 3 users A, B & C
The network path for example is : S:Department1TeamAConsolidated Report"
The spreadheet name is : Consolidated Database.xls
If the spreadheet that is on S:Department1TeamAConsolidated ReportConsolidated Database.xls is already open by User A then User B should get a message that "File is already open"
I have the below code which I got from a website which I kind of tweaked but it doesnt work.
Dim WbookCheck As Workbook
On Error Resume Next
Set WbookCheck = Workbooks("Consolidated Database.xls ")[code]......
View 5 Replies
View Related
Dec 28, 2007
what I want. It relies on an input box asking the user to enter a part code, which when found creates a duplicate copy. However, if the user enters a part that doesn't exist I get a run-time error. Can anybody point me in the right direction so when a user enters an incorrect part they get a message telling them it doesn't exist.
Private Sub SortTest_Click()
Dim s As String
Dim r As Excel. Range
Range("A2").Activate
s = InputBox("Enter the number you wish to find")
If StrPtr(s) = 0 Then
MsgBox "You must enter an existing part number!"
Else
Set r = Cells.Find(What:=s, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
Range(Cells(r.Row, "A"), Cells(r.Row, "AH")).Copy
Sheets("APL").Cells(r.Row, "A").Insert Shift:=xlDown
Application.CutCopyMode = False
Application.Goto Sheets("APL").Cells(r.Row, "H")
Selection.Offset(-1, -5).ClearContents
Selection.Offset(-1, 0).Select
End If
End Sub
View 7 Replies
View Related
Sep 4, 2009
my macros to search for the word "Cancel" in columns "T". Once found, I need the macros to make that entire row a certain pattern ( .Pattern = xlGray16)
There will be other wording in these cells that contain "Cancel" or "Cancelled".
A compile error keeps occurring. It says "Ambiguous name detected: FormatCell"..
View 14 Replies
View Related
Nov 2, 2006
I want to include a progress bar in my macro and have got the code from another website. However, after the progress bar is displayed i cannot get it to move to the next line of code in the module. In the example i've got, the code in the user form window simply calls a sub called "main" and continues on through that sub. Whenever i call one of my sub routines an error box states" ambiguous name detected". This never happens with the sub called "main"? Why is this happening? A portion of the code is shown below. After "userform1.show", the user form is displayed but the program will not move on to "get_blades" sub routine?
Browse_Folder
' Sub routine "browse_folder" selected to select folder
' containing the mea files.
UserForm1.LabelProgress.Width = 0
UserForm1.Show
' HourGlass
get_blades (lookinname)
' Use sub routine get_blades
Range("T3").Select
sort_data
View 7 Replies
View Related
Oct 24, 2007
Option Explicit
Public Const ARK As String = "Inddata-ARK"
Public Const VVS As String = "Inddata-VVS"
Public Const KON As String = "Inddata-KON"
Public Const EL As String = "Inddata-EL"
Public Const LD As String = "Inddata-LD"
Public Const KØL As String = "Inddata-KØL"
Public Const OpDat As String = "Opdatering"
And then macro's under it... So the aboce is in module "INIT_S". Now in another module "RUN_P" I have a sub-rutine that uses these constants with a select case.
Likes this:
Sub OpEksInitNu()
Select Case ActiveSheet. Name
Case ARK
With OpEks
'more stuff
Case VVS
'more stuff
Case KON
'more stuff
End Sub
But when I try to run OpEksInitNu i comes with ambigious name with ARK as the error, later down I use another public constant. But i keep getting the ambigious name error.. What could cause that... I have tried to locate all ARK, VVS etc. to try to determine if it is used later on, but it's not! I only declare the constants one time, and no module is called ARK, VVS etc.
View 2 Replies
View Related
Sep 12, 2009
I have userform in excel which asks user to enter user id and password. I want a to have "Remember Me" check button on my userform which will remember user details and he wouldn't have to enter user id and password again and again.
View 14 Replies
View Related
Feb 16, 2010
Hello, Im using a script that allows me to autocomplete a data validation list using a combo box and the code shown below. However I am getting a compile error ambigous name detected and the following line is highlighted
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)"
I know its because this is used twice but I am not sure what to do in order to fix this.
View 11 Replies
View Related
Nov 5, 2013
I need to have this spread sheet date when a cell is changed. I have set up the Visual Basic editor and it works for the column D when updated puts the date in column E.
But I have duplicated what I want for the other columns but I get ambiguous name detected and the other columns do not work my knowledge of macros has hit its limit ...
View 4 Replies
View Related
Nov 2, 2006
I want to include a progress bar in my macro and have got the code from another website. However, after the progress bar is displayed i cannot get it to move to the next line of code in the module. In the example i've got the code in the user form window simply calls a sub called "main" and continues on through that sub. Whenever i call one of my sub routines an error box states" ambiguous name detected". This never happens with the sub called "main"? Why is this happening?
View 2 Replies
View Related
Oct 28, 2008
Receiving the following error:
Compile Error:
Ambiguous name detected: Worksheet_Change
How can I resolve this? Is there a way to combine these two (2) statements?
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$O$3" Then
Range("B2:L13").ClearContents
Range("A17:A27").Copy
Sheets("Letter").Range("A16:A26").PasteSpecial xlValues
Application.CutCopyMode = False
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "TextBody" Then
Range("A17:A27").Copy
Sheets("Letter").Range("A16:A26").PasteSpecial xlValues
Application.CutCopyMode = False
End If
End Sub
View 5 Replies
View Related