| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
If Macro Run From Form Control Button/Shape, "Find" Command Not Working
I have an unusual problem and I'm not sure how to fix it. I'm using excel 2007 and I have a workbook that has a series of form buttons programmed to run macros.
On one of the buttons, I have a "stop" command in the macro so that I can manually do a find on a name and then I contine the macro. For some reason, when the macro is launched from the button, pressing "Ctrl+F" or writing the "find" function in the macro, does not appear to do anything. I don't even get a "cannot find what you're looking for" dialog. What I found is that when the button is pressed, whatever cell is currently selected becomes the "find" area, which is why it is not finding anything. When I select all the cells and do a find, it finds what I'm looking for. I have tried both a shape and a button to run the macro and both produce the same outcome. I tried to attach a sample workbook to illustrate my problem, but it wouldn't accept the attachment. It should be easy enough to reproduce: 1. Open a new excel workbook and create a new macro with just the word "Stop" in the sub-routine. 2. In the excel workbook, create a form button or shape and assign the macro you just wrote to the button. 3. Write the word "Holiday" in cell "A3" 4. Press the button to run the macro. 2. When the macro stops, select any cell in the workbook (except for cell A3) and do a Ctrl+F and search for "Holiday." You'll see that nothing happens. 3. Select all the cells and do the same thing, and the cell with "Holiday" will be outlined. Does anyone know why this happens and is there a solution? Thanks! Last edited by KeriM : September 8th 14 at 04:07 PM |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Macro Run From Form Control Button/Shape, "Find" Command Not Working
On Monday, September 8, 2014 10:04:20 AM UTC-5, KeriM wrote:
I have an unusual problem and I'm not sure how to fix it. I'm using excel 2007 and I have a workbook that has a series of form buttons programmed to run macros. On one of the buttons, I have a "stop" command in the macro so that I can manually do a find on a name and then I contine the macro. For some reason, when the macro is launched from the button, pressing "Ctrl+F" or writing the "find" function in the macro, does not appear to do anything. I don't even get a "cannot find what you're looking for" dialog. What I found is that when the button is pressed, whatever cell is currently selected becomes the "find" area, which is why it is not finding anything. When I select all the cells and do a find, it finds what I'm looking for. I have tried both a shape and a button to run the macro and both produce the same outcome. I tried to attach a sample workbook to illustrate my problem, but it wouldn't accept the attachment. It should be easy enough to reproduce: 1. Open a new excel workbook and create a new macro with just the word "Stop" in the sub-routine. 2. In the excel workbook, create a form button or shape and assign the macro you just wrote to the button. 3. Write the word "Holiday" in cell "A3" 4. Press the button to run the macro. 2. When the macro stops, select any cell in the workbook (except for cell A3) and do a Ctrl+F and search for "Holiday." You'll see that nothing happens. 3. Select all the cells and do the same thing, and the cell with "Holiday" will be outlined. Does anyone know why this happens and is there a solution? Thanks! -- KeriM post your macro |
|
#3
|
|||
|
|||
|
Quote:
Code:
Sub Test() Stop End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Macro Run From Form Control Button/Shape, "Find" Command Not Working
Code:
-------------------- Sub Test() Stop End Sub -------------------- At the "Stop" is when I'd try to do a find on the workbook while the report was still running the macro. You can't execute another macro via the UI so as to interupt code that is already executing. You have to wait for it to finish. What you can do is prompt the user for 'Find' criteria and have the running code process that. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
|
#5
|
|||
|
|||
|
Quote:
For some reason, when the macro is initiated by a button press, the "find" does not search the whole workbook and only searches the currently selected cell. If I initiate the macro within the VBA window, this does not happen. What I want to know is why. Last edited by KeriM : September 11th 14 at 02:42 PM |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Macro Run From Form Control Button/Shape, "Find" Command Not Working
'GS[_2_ Wrote:
;1618581'] Code:- -------------------- Sub Test() Stop End Sub -------------------- At the "Stop" is when I'd try to do a find on the workbook while the report was still running the macro.- You can't execute another macro via the UI so as to interupt code that is already executing. You have to wait for it to finish. What you can do is prompt the user for 'Find' criteria and have the running code process that. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion I know that. I'm not trying to execute another macro while a macro is running. I'm pausing the current macro to manually do a find (ctrl + F) on another sheet in the workbook to fill in data gaps due to data entry errors and then continue the macro that I paused. For some reason, when the macro is initiated by a button press, the "find" does not search the whole workbook and only searches the currently selected cell. If I initiate the macro within the VBA window, this does not happen. What I want to know is why. While code is "in play", Excel usually forbids any interaction until that code "ends" -OR- the code itself prompts the user for interactive input via an appropriate method. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
|
#7
|
|||
|
|||
|
Quote:
Quote:
That is usually correct, but I am able to interact with the program when the code pauses at a "stop" command. I know this works because I can do it when I don't initialize the macro with the button. I'm not questing whether or not it will or should work, because I know it does since I've been doing it without the button for a few months now with no issues. Something different happens when I click the button vs just running the macro directly from the VBA window. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Macro Run From Form Control Button/Shape, "Find" Command Not Working
KeriM;1618609 Wrote:
I know that. I'm not trying to execute another macro while a macro is running. I'm pausing the current macro to manually do a find (ctrl + F) on another sheet in the workbook to fill in data gaps due to data entry errors and then continue the macro that I paused. For some reason, when the macro is initiated by a button press, the "find" does not search the whole workbook and only searches the currently selected cell. If I initiate the macro within the VBA window, this does not happen. What I want to know is why. 'GS[_2_ Wrote: ;1618612'] 'GS[_2_ Wrote: -- ;1618581'] Code:-- -------------------- Sub Test() Stop End Sub -------------------- At the "Stop" is when I'd try to do a find on the workbook while the report was still running the macro.-- You can't execute another macro via the UI so as to interupt code that is already executing. You have to wait for it to finish. What you can do is prompt the user for 'Find' criteria and have the running code process that. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion- I know that. I'm not trying to execute another macro while a macro is running. I'm pausing the current macro to manually do a find (ctrl + F) on another sheet in the workbook to fill in data gaps due to data entry errors and then continue the macro that I paused. For some reason, when the macro is initiated by a button press, the "find" does not search the whole workbook and only searches the currently selected cell. If I initiate the macro within the VBA window, this does not happen. What I want to know is why.- While code is "in play", Excel usually forbids any interaction until that code "ends" -OR- the code itself prompts the user for interactive input via an appropriate method. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion Garry, That is usually correct, but I am able to interact with the program when the code pauses at a "stop" command. I know this works because I can do it when I don't initialize the macro with the button. I'm not questing whether or not it will or should work, because I know it does since I've been doing it without the button for a few months now with no issues. Something different happens when I click the button vs just running the macro directly from the VBA window. There are some different behaviors when using 'Debug' mode, but even that disables Excel's menus/keys. Show your actual macro with commented 'stop' point and action to take while stopped... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
|
#9
|
|||
|
|||
|
Quote:
I still cannot upload a sample workbook, I get an "invalid file" error. The macro I gave earlier is a simple example of what triggers it. Please follow the steps below to reproduce what I am experiencing. 1. Put the following macro in a new workbook Code:
Sub Test()
Range("D3").Select
Stop
End Sub
3. Create a button or a shape below cell A1 and assign the macro you just wrote to the object. 4. Press the button to run the macro. 5. When the macro pauses at the stop, click into the excel workbook and press "ctrl F" and search for the word "Holiday". 6. You'll notice that the mouse curser is a "+" sign and that it will not find the word. 7. Continue the macro until it stops. 8. Rerun the macro by clicking into the sub procedure itself and running it. 9. When the macro stops, click into the excel workbook and press "ctrl F" and search for the word "Holiday." 10. You'll see that the mouse curser will be normal and it will find the word. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Macro Run From Form Control Button/Shape, "Find" Command Not Working
Keri,
I tested this in XL2010! In both case scenarios, Ctrl+F worked as expected! I placed "Holiday" in A1. Case1: When D3 was selected after clicking the button I did Ctrl+F, typed "Holiday" into the 'Find what' box, then clicked 'Find All'. Result: A1 selected Case2: Using F8 in VBE, at the line 'Stop' I did Alt+F11, Ctrl+F and click 'Find Next'. Result: A1 selected -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
|
#11
|
|||
|
|||
|
Quote:
I appreciate you testing it. I'm using Excel 2007, so I wonder if it's a bug with this version. I'm not even sure the proper keyword to google this problem to find out if anyone else has this issue. Hopefully someone with Excel 2007 comes across this thread and tests it out as well. Thank you again. |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Macro Run From Form Control Button/Shape, "Find" Command Not Working
'GS[_2_ Wrote:
;1618625']Keri, I tested this in XL2010! In both case scenarios, Ctrl+F worked as expected! I placed "Holiday" in A1. Case1: When D3 was selected after clicking the button I did Ctrl+F, typed "Holiday" into the 'Find what' box, then clicked 'Find All'. Result: A1 selected Case2: Using F8 in VBE, at the line 'Stop' I did Alt+F11, Ctrl+F and click 'Find Next'. Result: A1 selected -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion Garry, I appreciate you testing it. I'm using Excel 2007, so I wonder if it's a bug with this version. I'm not even sure the proper keyword to google this problem to find out if anyone else has this issue. Hopefully someone with Excel 2007 comes across this thread and tests it out as well. Thank you again. I'll retest in 2007... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
|
#13
|
|||
|
|||
|
Quote:
|
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Macro Run From Form Control Button/Shape, "Find" Command Not Working
'GS[_2_ Wrote:
I'll retest in 2007... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion Perfect! Thank you! Ok.., I tested this all the way back to v9 and I find the behavior as you state. Versions 10 thru 14 all have 'Find All' and this works via the UI after clicking the button, but 'Find Next' does not *even if an instance exists after D3*. In all cases 'Find Next' works when stepping through the macro. As I already suggested earlier, this is an example of the difference in behaviors while in 'Debug' mode. It looks like you'll need to use 'Find All' via the UI. Note, though, that Excel will select the first occurance starting in A1 and searching forward. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to have a macro simply issue the "find" command or "control f: | Excel Programming | |||
| "Form / Button" Macro for Unhiding Sheets | Excel Discussion (Misc queries) | |||
| Macro for "Next" Button on User Form? | Excel Programming | |||
| command button and the "enter" and "tab" keys | Excel Programming | |||
| set up a command button or macro for open file to "save as" | Excel Programming | |||
Linear Mode
