Solved

Using Excel VBA to search for "Filled" cell

Posted on 2014-01-13
4
653 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
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
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.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

740 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