Solved

Excel search screen locks the "Fill" selection

Posted on 2014-01-24
4
378 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 500 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

840 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