Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel search screen locks the "Fill" selection

Posted on 2014-01-24
4
Medium Priority
?
406 Views
Last Modified: 2014-01-24
My question is obscure, but I'm going to go for it anyway.  I have a VB.Net form that opens a Search dialogue box to do a search based on the color in which the cells are filled.  I'm using this piece of code to invoke the search dialogue (MyApp is an Excel Application object):

MyApp.CommandBars("Edit").Controls("Find...").Execute()

Open in new window

The action is to select the formatting screen and select a fill option.  The code execution then does the following: 1) protect every cell in the worksheet; 2) search for the cells that have the fill option chosen in the search dialogue and strategically unprotect these cells; and 3) protect the worksheet with a predesignated password.  The effect is to allow editing in all the cells that have a particular highlight and disallow it everywhere else.  

Usually, the program works fine the first time around.  But when I use the code line above in an attempt to select a new format, the color selection remains locked to its previously selected item.  Interestingly, the format box is the only part of the search box that is locked; I can set everything else.  And curiously, when I open the search dialogue with the Excel interface, not the code line above, the fill section is not locked.  

I surmise that something about my first search for a given format locks that parameter.  I'm wondering if there's either another coded way to open the dialogue to mimic the effect of opening it through the interface or a procedure that unlocks the fill selection after the code has done a search.  I know when I pose it that the question, though obscure, may be something other EE experts have encountered and overcome.  Here's hoping.  ~Peter Ferber

Dialogue locks fill section after a search, when opening via code
0
Comment
Question by:PeterFrb
  • 2
  • 2
4 Comments
 
LVL 18

Accepted Solution

by:
Steven Harris earned 2000 total points
ID: 39807377
It may have something to do with step 3) "protect the worksheet with a predesignated password."

If you were to comment out the protection, will the second search work?
0
 

Author Comment

by:PeterFrb
ID: 39807450
An excellent suggestion!  And it was through your suggestion that I actually found the answer!  The order in which I was operating was the reverse it should have been, as exhibited in the following code:

'The order of these two commands used to be in reverse, which caused it to fail for two reasons: 1) If the cells are locked, then the relevant cells for which to search don't register as a proper hit.
'2) With all the cells locked, the ability to change the search criteria in the search dialogue is compromised.  With this reversal, those two problems have gone away!
Dim GetRanges() As Excel.Range = FindRanges_Simple(UseSheet, strFeedback)
UseSheet.Cells.Locked = True

Open in new window


FindRanges_Simple performs the search and returns an array of ranges that match the search criteria.  Although I came up with the answer, I'm giving you the credit because your suggestion lead to discovering how to solve the problem.  Cheers!
0
 

Author Closing Comment

by:PeterFrb
ID: 39807886
See my more elaborate points in my last post.  Thanks!
0
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39807943
I'm glad you got it fixed, and that is all that truly matters!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

877 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