Sabtu, 14 Februari 2015

[ExcelVBA] How do you set a linked cell from a macro

 

Hi there!


I have an Activex based dynamic search tool with conditional formatting run from a macro. One thing that so far I have failed to achieve automation is setting the linked cell in textbox properties without actually doing it manually.

Any and all help would be appreciated.

Clive

<code>
Sub SearchMe()
    Sheets("Sheet3").Select
    Range("E2").Select
    ActiveSheet.OLEObjects.Add(ClassType:="Forms.TextBox.1", Link:=False, _
                               DisplayAsIcon:=False, Left:=192.75, Top:=15, Width:=60.75, Height:= _
                               21.75).Select
    Range("E9").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
                                   "=AND($E$2 <> """",ISNUMBER(SEARCH($E$2,E9,1)))"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("E9").Select
    Selection.Copy
    Range("E10:E100").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
                           SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    Range("C13").Select
End Sub

</code>

__._,_.___

Posted by: "Clive Jones" <calmsoft09@yahoo.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

.

__,_._,___

Tidak ada komentar:

Posting Komentar