Delete macro button (2003)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

The button is from the forms toolbar

Reply | Quote

Try this instruction:

ActiveSheet.Shapes(Application.Caller).Delete

Reply | Quote

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?

Reply | Quote

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…?

Reply | Quote

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

Reply | Quote

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…?

Reply | Quote

Number 1 please Hans.

Reply | Quote

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

Reply | Quote

Hans,
It worked a treat.
Thanks

Reply | Quote