Results 1 to 11 of 11
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete macro button (2003)

    I have a macro that is executed from a button. At the end of the macro,I want to delete the button.

    How do I do this,I am unsure of the button's properties (object)??

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete macro button (2003)

    How did you create the button? From the Forms toolbar or from the Control Toolbox toolbar?

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete macro button (2003)

    The button is from the forms toolbar

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete macro button (2003)

    Try this instruction:

    ActiveSheet.Shapes(Application.Caller).Delete

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete macro button (2003)

    Thankyou Hans.

    The sheet has 6 different buttons, is this specific to the button that called the procedure?

    May I also ask another question please.. (on a different subject)

    I have set my range to rg1. Within the range, I want to insert a formula only if:

    Column A = NON 6506 ITEMS

    And

    Column H = HBBA 99 6506

    I have

    rg1.Formula = "=value(mid(B2,11,5))"

    but need to build in the 2 conditions. How do I do that?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete macro button (2003)

    1) In this situation, Application.Caller returns the name of the specific button that called the macro.

    2) What do you mean by "Column A = NON 6506 ITEMS"? Should a specific cell in column A contain this value, or any cell, or...?

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete macro button (2003)

    The range is variable.

    Eg: Sheet 'rejections 2' column M from row 2 until end of data (backwards).

    The formula should be input to the range (column M) if column A = NON 6506 ITEMS and column H = HBBA 99 6506

    Hope this makes sense, I have attached the code incase.

    Thanks
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete macro button (2003)

    What exactly do you want?

    1) Only insert the formula in cells in column M where cells in the same row in columns A and H meet the criteria.

    - or -

    2) Insert a formula in all cells but make it return a blank if the cells in the same row in columns A and H meet the criteria.

    - or -

    3) Something else...?

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete macro button (2003)

    Number 1 please Hans.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete macro button (2003)

    You'll have to create a loop:

    Dim r1 As Long
    For r1 = 2 To q1
    If Range("A" & r1) = "NON 6506 ITEMS" And Range("H" & r1) = "HBBA 99 6506" Then
    Range("M" & r1).Formula = "=Value(Mid(B" & r1 & ",11,5))"
    Else
    Range("M" & r1).ClearContents
    End If
    Next r1

  11. #11
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete macro button (2003)

    Hans,
    It worked a treat.
    Thanks <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

Posting Permissions

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