calling a macro from a cell formula
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: calling a macro from a cell formula

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a worksheet that is hidden when activatated and asks for a password before viewing. I wanted it to ascend the cells each time is was deactivated, but couldn't get that to work so I added a command button for the user to click to ascend the cells in the event of new entries. That worked fine but now the sheet will not hide(error message saying cannot hide), is this due to the command button being added? If so is their any property adjustments I need to do to the command button so that it will hide(I'm assuming that is the reason the page doesn't hide because the button won't hide)? Or any code adjustment I need to make? As far as calling a macro, I have a cell that I have thought about using to replace the command button. If the user enters the word "ascend" into the cell it will call the macro I used for the command button. But I can't figure out how to call a macro from a cells formula. Any help?

    Thanks,
    I appreciate the help from everyone at Mr. Excel.

    viper

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I don't think it's the command button that's causing your sheet not to be hidden. If it's not the only sheet in the workbook then you *should* be able to hide it, but without seeing your workbook and code, I couldn't say for sure.

    As for the second question, add this code to your worksheet- it'll run the procedure called 'YourMacro' anytime anyone enters 'ascend' into cell A1 (case-insensitive, change as necessary for the name of your macro and the cell you want to run it from): -

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub
    If Target.Address <> "$A$1" Then Exit Sub
    If UCase(Target.Value) = "ASCEND" Then Call YourMacro

    End Sub

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thanks,

    Worked great!
    I appreciate the help from everyone at Mr. Excel.

    viper

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com