Solved

Using Excel VBA to search for "Filled" cell

Posted on 2014-01-13
4
664 Views
Last Modified: 2014-01-21
Hi,

I'm using VBA in Excel 2010, and what seems like it should be a straightforward problem is turning out to have a wrinkle somewhere.  My client uses a "Fill" to highlight the cells that he wants to allow users to edit.  Everything not so shaded should be protected from editing.

The method for doing this is to start by protecting every cell, then search for the proper highlight and unprotect those.  By setting a HoldRange at the start of the search, I am assured that the procedure will go through its paces only once and then stop, because it will come back around on itself.  I then manually protect the sheet, at which point I can edit anything that's appropriate for me to do so, and I get an appropriate error message when attempting to edit a non-highlighted cell.  Although this process works, the catch is that when I record a macro to do the search and then run that search, the search fails, mysteriously.  As long as I "seed" it with a proper manual search, the code below works as advertised:

Sub find_Highlight_And_Unlock(UseSheet As Worksheet)
    Dim UseRange As Excel.Range
    Dim HoldRange As Excel.Range
    
    Dim lngCount As Long
    
    UseSheet.Activate
    Set UseRange = UseSheet.Range("A1")
    UseRange.Activate
    UseSheet.Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=True).Activate
    Set HoldRange = UseSheet.Parent.activeRange
    HoldRange.Locked = False
    HoldRange.FormulaHidden = False
    ActiveSheet.Cells.Locked = True
    While (HoldRange.Address <> UseRange.Address)
        Set HoldRange = _
            Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=True).Activate
        Selection.Locked = False
        Selection.FormulaHidden = False
    Wend
End Sub

Open in new window


When I record myself searching for a highlight, VBA autogenerates this code, which doesn't succeed in finding anything when run:

With Application.FindFormat
    .Clear
    With .Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark2
        .TintAndShade = -9.99481185338908E-02
        .PatternTintAndShade = 0
   End With
End With

Open in new window


I would like for my code to work as a standalone, without the manual "seeding".  In fact, even better would be if my code could open the dialogue box that allows me to set the search format at runtime and then unlock the relevant highlighted cells.  Writing code to open the search dialogue box would be more intuitive and flexible than what I have now: a fixed highlight that doesn't work anyway.  

Any thoughts on how to do this?  ~Peter Ferber
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
4 Comments
 
LVL 30

Expert Comment

by:hnasr
ID: 39778427
Upload a sample, with few rows of data.
Specify the requirement referring to the sample.
0
 

Author Comment

by:PeterFrb
ID: 39780028
Actually, I've made progress on my question through independent research.  This link does well in addressing my question:

http://www.mrexcel.com/forum/excel-questions/71530-visual-basic-applications-find-dialog-box.html

The key piece of code is this one:

Application.CommandBars.FindControl(ID:=1849).Execute

Open in new window

Having come this far, the difficulty I now have is that the code following this routine runs before I've made the selection here,  I would prefer that the code following this routine wait until I've finished filling in the search parameter before running, but that is not happening.  The following command opens a find dialogue and holds the code execution until I've said "OK", but that dialogue only allows for the searching of text, not formatting it.

Application.Dialogs(xlDialogFormulaFind).Show

Open in new window

My holy grail is the "Show" facility with a dialogue that allows me to format cells.  Between the two, I have those two features, but not in the same dialogue.  The work shows undeniable progress, though, for which I'm grateful.  ~Peter
0
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 500 total points
ID: 39793663
what about the user selecting any one of the desired formatted cells ?

  You can return a range object with the users selections and get the desired format from it.
0
 

Author Closing Comment

by:PeterFrb
ID: 39797802
Yes, I got this working, and that's what I did.  I do prefer, when you give a suggestion, that you give a basic rundown of how to do that thing.  Sincerely, ~Peter
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

732 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