Solved

Excel search screen locks the "Fill" selection

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

759 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