Disabling macros once a commandbutton is clicked

Had the same problem, this problably wrong, but I placed a letter into a hidden cell when then action was done, then the next time it exit sub if you look for that letter in the location you put it

Private Sub CommandButton1_Click()

If Range("b44").Value = "A" Then
Exit Sub
ElseIf ThisWorkbook.SaveAs "C\Tests\Quotations\" & Range("QuotationNumber")
Range("b44").Value = "A"
CommandButton1.Visible = False
ThisWorkbook.Protect
ThisWorkbook.Save
else
end if
end sub

I hope someone can give me a better answer but...

Any help

Ian

Matt

I assume you call the "Quote" macro by way of "Workbook_Open" or an Auto_Open macro.

If you never want the code to run again, it may be best to delete it altogether. You can do this via code so long as the Project is not locked.

Code is something like this:

Dim myLIne As Long, allLines As Long
myLIne = Application.VBE.ActiveVBProject.VBComponents("Module1").CodeModule.ProcStartLine("QUOTE", vbext_pk_Proc)
allLines = Application.VBE.ActiveVBProject.VBComponents("Module1").CodeModule.ProcCountLines("QUOTE", vbext_pk_Proc)
Application.VBE.ActiveVBProject.VBComponents("Module1").CodeModule.DeleteLines myLIne, allLines

You would need to amend the code to point to the module in which you have the macro. Then include it in the Button1_Click code instead of the Enabled statement.

Any help?

Regards

Matt

If you are calling the "Quote" macro, you may need to remove the call line from the code as well.

If it is the only call from an "auto" macro, just repeat the code (amended) to remove the calling macro as well.

If it does other thing, better to just remove the calling line. You will need to count down to it - start right at the very top of the module code pane and count down.

Assuming it is line 23, the code would be:

Application.VBE.ActiveVBProject.VBComponents("ThisWorkbook").CodeModule.DeleteLines 23, 1

You will ned to replace "This Workbook" if that is not where the macro is.

Any problem, post again - unfortunately I would not be able to reply until overnight.

Hope this helps

Regards

Matt I assume you call the "Quote" macro by way of "Workbook_Open" or an Auto_Open macro. If you never want the code to run again, it may be best to delete it altogether. You can do this via code so long as the Project is not locked. Code is something like this:

: Hi, : This is beginning to get on my nerves now. I'm trying to create a worksheet that has a command button that saves the document ot a new location but in doing so removes the command button and disables the macro that force a userform to appear once the document is oppened. I can get rid of the commandbutton but the userform keeps appearing. Any ideas?? : This is the code that i'm attempting to use... : Private Sub CommandButton1_Click() : ThisWorkbook.SaveAs "C:\Tests\Quotations\" & Range("QuotationNumber") : Macro.Quote.Enabled = False : CommandButton1.Visible = False : ThisWorkbook.Protect : : ThisWorkbook.Save : End Sub : I've tried variations such as Welcome (the userforms name).Hide : Welcome.Enabled = false : I'm using excel 97 : Cheers, : Matt

Matt

Possibly a simpler solution is a "workaround" way of doing it.

Include a value somewhere in the workbook - say something like an A in a cell.

Let's assume this is in Sheet1 Range a1.

In the macro that calls the UserForm, include:

If Worksheets("Sheet1").Range("A1") = "A" then
{your cade to call the userform]
Else
End If

In the CommandButton1_Click code, replace the Macro Enabled statement with:

Worksheets("Sheet1").Range("a1").ClearContents

After that, since there is no value in Range("a1"), the calling macro will no longer invoke the UserForm.

Sorry to be so convoluted, but I hope it helps.

Regards

If you are calling the "Quote" macro, you may need to remove the call line from the code as well. If it is the only call from an "auto" macro, just repeat the code (amended) to remove the calling macro as well. If it does other thing, better to just remove the calling line. You will need to count down to it - start right at the very top of the module code pane and count down. Assuming it is line 23, the code would be: Application.VBE.ActiveVBProject.VBComponents("ThisWorkbook").CodeModule.DeleteLines 23, 1 You will ned to replace "This Workbook" if that is not where the macro is. Any problem, post again - unfortunately I would not be able to reply until overnight. Hope this helps Regards : Dim myLIne As Long, allLines As Long : myLIne = Application.VBE.ActiveVBProject.VBComponents("Module1").CodeModule.ProcStartLine("QUOTE", vbext_pk_Proc) : allLines = Application.VBE.ActiveVBProject.VBComponents("Module1").CodeModule.ProcCountLines("QUOTE", vbext_pk_Proc) : Application.VBE.ActiveVBProject.VBComponents("Module1").CodeModule.DeleteLines myLIne, allLines : You would need to amend the code to point to the module in which you have the macro. Then include it in the Button1_Click code instead of the Enabled statement. : Any help? : Regards :

Thanks lot!! Could you check this out?

Thanks a lot, your on a roll!!

I've now cracked it using your help. Thanks again.

I don't know if you could help me with this problem?

I have a range of values that i want to check. They are total numbers of stock that i want to check for values below, say 5. If they have a value below such a figure i want to add the total and the product description to a list box on a user from.

I've labelled the ranges "totals" and "description" but can't seem get excel to place stock with a value belwo 5 in the listbox. I would also like (if possible) to chnage the level before they get entered into the listbox of certain types of stock but that should be relativly easy once the initial problem is cracked.

Any help would be appreciated.

Cheers (and thanks again)

Matt

Re: Thanks lot!! Could you check this out?

Matt

Is your ListBox bound or unbound?

I'm not absolutely sure if this is what you are trying to do, but try this code.

You will need a UserForm with a CommandButton1 and ListBox1 (or you will need to change those references in the code). You will have to play with the ranges to suit your situation, but the result is that the values in the
2 columns are concatenated and added to the ListBox.

Private Sub CommandButton1_Click()
On Error Resume Next
For Each r In Worksheets("Errors").UsedRange.Rows
n = r.Row
If Worksheets("Whatever").Cells(n, 3) = "2108" And Worksheets("Whatever").Cells(n, 4) <> "" Then
myentry = Worksheets("Whatever").Cells(n, 3) & Worksheets("Whatever").Cells(n, 4)
ListBox1.AddItem (myentry)
Else
End If
Next r
End Sub

Any help?

Regards

Regards

Thanks a lot, your on a roll!! I've now cracked it using your help. Thanks again. I don't know if you could help me with this problem? I have a range of values that i want to check. They are total numbers of stock that i want to check for values below, say 5. If they have a value below such a figure i want to add the total and the product description to a list box on a user from. I've labelled the ranges "totals" and "description" but can't seem get excel to place stock with a value belwo 5 in the listbox. I would also like (if possible) to chnage the level before they get entered into the listbox of certain types of stock but that should be relativly easy once the initial problem is cracked. Any help would be appreciated. Cheers (and thanks again) Matt