Consulting

Results 1 to 3 of 3

Thread: cant edit cells from vba

  1. #1
    VBAX Newbie
    Joined
    Jan 2008
    Posts
    2
    Location

    Lightbulb cant edit cells from vba



    I wrote this function
    [vba]
    Function Test(r as Range)
    R(1,1).Font.Color=VbRed
    End Function
    [/vba]
    Now when I call this function in a cell Formula
    [vba]=Test(A1:C3)[/vba]
    Nothing changed, like the cell is in lock to changes, but when I call the function from UserForm its work.
    Any idea
    Thanks

  2. #2
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,776
    UDF's (User Defined Functions) return values. They don't change the environment, like changing cell colors, cell size, sheet names, etc.
    In some cases, you can squeek something through when the UDF is called from other VB routine, but Excel is very strict about this when the UDF is called from a spreadsheet formula.

    To change a cell's color, use the formatting controls. Or Condiditonal Formatting might better suit your needs.

  3. #3
    VBAX Newbie
    Joined
    Jan 2008
    Posts
    2
    Location
    Quote Originally Posted by mikerickson
    UDF's (User Defined Functions) return values. They don't change the environment, like changing cell colors, cell size, sheet names, etc.
    In some cases, you can squeek something through when the UDF is called from other VB routine, but Excel is very strict about this when the UDF is called from a spreadsheet formula.

    To change a cell's color, use the formatting controls. Or Condiditonal Formatting might better suit your needs.

    thanks for your answer.
    I now understand the limitation.
    so I guess I will stick with the GUI method

    thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •