Solved

Excel search screen locks the "Fill" selection

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now