Solved

Creating a modal search dialogue in Excel VBA

Posted on 2014-01-14
3
340 Views
Last Modified: 2014-01-16
I want to retrieve all the cells on a worksheet that are formatted the same way.  I have two methods of opening the formatting search dialogue, but neither method is modal:

1: Application.CommandBars.FindControl(ID:=1849).Execute 
2: Application.CommandBars("Edit").Controls("Find...").Execute 

Open in new window


The problem with a non-modal search dialogue is that my code does not wait for me to close it before continuing with code execution.  As an alternate method, if there were a worksheet or workbook event that were savvy enough to track and run after I closed the formatting dialogue, then my followon code could take over from that point.  Any notion of how to do this?  Below is a screenshot of the formatting dialogue.  Thanks, ~Peter Ferber

Search on highlighted cells, not text
0
Comment
Question by:PeterFrb
3 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39782397
Display a msgbox after showing the dialog.
0
 
LVL 43

Accepted Solution

by:
Rob earned 500 total points
ID: 39784304
The Event,

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
   
End Sub

will capture when the selection is changed in the worksheet.
0
 

Author Closing Comment

by:PeterFrb
ID: 39787424
Oooh, sweet.  Thanks much!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question