Solved

Using Excel VBA to search for "Filled" cell

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Suggested Solutions

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

789 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