Solved

Using Excel VBA to search for "Filled" cell

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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 …
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

821 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