Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel search screen locks the "Fill" selection

Posted on 2014-01-24
4
Medium Priority
?
392 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

719 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