How do you disable or delete a button in VBA?

grimlock

New Member
Joined
Mar 9, 2009
Messages
39
How do you disable or delete a button in VBA code after the button is pressed?

Thanks.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
This works for an activeX command button on a sheet:
Code:
Private Sub CommandButton1_Click()
Me.CommandButton1.Enabled = False
End Sub

This bugs out though:
Code:
Private Sub CommandButton2_Click()
Me.CommandButton2.Delete
End Sub


What are you trying to do?
 
Upvote 0
Thanks for responding.
What I am trying to do is (with a button):
Copy "Template" worksheet
Rename it "PCO 1"
Disable or delete button (Button will only be used once)
The second button will generate "PCO 1 rev 1" and the third "PCO 1 rev 2" and so on, up to rev 5. Then the following button will generate "PCO 2", followed by the same series of "rev 1 to 5" buttons. I need to design it to reach "PCO 100". So I guess in total (6 x 100) I would have to make 600 buttons..... Makes my head hurt just thinking about it.
Thanks for any ideas.


Recorded macro:
Sub TEST2()
'
' TEST2 Macro
'
'
Sheets("Template").Select
Sheets("Template").Copy After:=Sheets(3)
Sheets("Template (2)").Select
Sheets("Template (2)").Name = "PCO 1"
End Sub
 
Upvote 0
Yes, very undesireable.

Try this - its a little rough but maybe will work. It simply calculates the next PCO number needed for you, but you could click the same button each time. It would be faster if we assumed the sheets stay in incremental order but I am not assuming that as who knows what your user might do (unless you are the only user).

Not tested yet.

Code:
Private Sub CommandButton1_Click()
[COLOR="SeaGreen"]'
'Purpose:
'To increment sheet names in the following manner:
'PCO 1 rev 1
'PCO 1 rev 2
'PCO 1 rev 3
'PCO 1 rev 4
'PCO 1 rev 5
'PCO 2 rev 1
'... etc. with 5 revisions for each PCO number, up to 100 PCO numbers
'This macro does not require sheets to be in order.
'This macro does require that all PCO sheets have exactly 11 characters in the above form.
'---------------------------------------------------------------------[/COLOR]
Dim ws As Worksheet

Set ws = Sheets("Template").Copy(After:=Sheets(Sheets.Count))
ws.Name = NextPCO()

End Sub
'-----------------------------------
Private Function NextPCO() As String
Dim ws As Worksheet
Dim i As Integer [COLOR="seagreen"]'temp variable[/COLOR]
Dim p As String [COLOR="seagreen"]'PCO Number - 1 to 100[/COLOR]
Dim r As String [COLOR="seagreen"]'Revision Number - 1 to 5[/COLOR]
Dim blnFound As Boolean
Dim a(0 To 1) As String

For Each ws In ThisWorkbook.Worksheets
    [COLOR="seagreen"]'//Find pco sheets.  They must have 11 characters[/COLOR]
    If (LCase(Left(ws.Name, 3)) = "pco") And (Len(ws.Name) = 11) Then
        blnFound = True [COLOR="seagreen"]'//flag that a pco already exists[/COLOR]
        p = Mid(ws.Name, 5, 1) [COLOR="seagreen"]'//pco number[/COLOR]
        r = Mid(ws.Name, 11, 1) [COLOR="seagreen"]'//revision number[/COLOR]
        
        [COLOR="seagreen"]'//determine if it is the highest pco number so far[/COLOR]
        If p = udfMaxInt(i, CInt(p)) Then
            i = CInt(p)
            a(0) = p
            a(1) = r
        End If
    End If
Next ws

[COLOR="seagreen"]'//if revision number is 5 then start a new series[/COLOR]
If a(1) > 4 Then
    a(0) = a(0) + 1
    a(1) = 1
End If

[COLOR="seagreen"]'//if pco number is 100 and revision is 5, STOP.[/COLOR]
If a(0) > 99 And a(1) > 4 Then
    MsgBox "PCO numbers cannot be greater than 100."
    End [COLOR="seagreen"]'//Complete stop.[/COLOR]
End If


[COLOR="seagreen"]'//return result[/COLOR]
If blnFound Then
    NextPCO = "PCO " & a(0) & " rev " & a(1)
Else
    NextPCO = "PCO 1 rev 1" [COLOR="seagreen"]'//we're on the first pco sheet[/COLOR]
End If

End Function
'----------------------------------------------------------------
Private Function udfMaxInt(ByVal arg1 As Integer, arg2 As Integer)
[COLOR="seagreen"]'Returns higher of two integers[/COLOR]
If arg1 > arg2 Then
    udfMaxInt = arg1
Else
    udfMaxInt = arg2
End If
End Function
 
Upvote 0
Thank you Alexander,
I tried your code and I recieve an error "Run-time error '424': Object required

under debug the following line is highlighted:
Set ws = Sheets("Template").Copy(After:=Sheets(Sheets.Count))

The Template worksheet is copied but not renamed, that’s where the error comes in.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
More info on how this workbook will be used:<o:p></o:p>
PCO stands for" Proposed Change Order". The "rev" or revisions will not be used too often but will be necessary when multiple versions of a particular PCO are required (when pricing is negotiated). So I suppose I will need to have one button for each PCO that first generates "PCO 1" followed by "PCO 1 rev1", "PCO 1 rev2", "PCO 1 rev3", "PCO 1 rev4" and finally with the sixth click "PCO 1 rev5". In addition, when a "rev" is produced I'll need to unhide a row (so information on the "rev" can be entered in the "PCO LOG" worksheet). After the sixth and final click the button would be disabled.
<o:p></o:p>
<o:p></o:p>
Greater detail on how used:<o:p></o:p>
The spreadsheet will be used for tracking change orders for large commercial electrical projects. there will be multiple copies of this spreadsheet, one for each project. Multiple estimators and project managers will be using the spreadsheets (my goal is to make them easy to use, at most computer experience levels). My design is going very well with the exception of the VBA coding.<o:p></o:p>
 
Last edited:
Upvote 0
Okay, yes that seems to be not allowed...

This will work in place:
Code:
Worksheets("Template").Copy After:=Worksheets(Worksheets.Count)
Set ws = ActiveSheet
ws.Name = NextPCO()

Thanks for the extra info...I'll await your further testing if this runs now...
 
Upvote 0
Thanks again for your time Alexander,

I tried your changes and it does get past the original problem, it seems to get stuck on this line in NextPCO() though:

If p = udfMaxInt(i, CInt(p)) Then

I couldn't figure out the cause..
 
Upvote 0
Where are the code and the button located?

If the code is in the Template worksheet there should be no need to delete and add a new button, just change it's name.

The button on the new sheet should refer to the code it's been copied along with.

Unless the code has hard-coded sheet names in it or is meant for some other purpose on the new worksheets.:)
 
Upvote 0
Alright...now for the debugging... :)

Try stepping through the code by hitting F8 in the code window. It should walk you line by line through the code...What is the value of p when it gets to this function, and does it "jump" into the udfMaxInt() function properly?

Also, I see a logic error. I'm using p = Mid(ws.Name, 5, 1) to get the current sheet pco number but that's only one digit.

Maybe try: p = Trim(Mid(ws.Name, 5, 2))


What is the value of p at the point where it bugs? If you are stepping through with F8 you can use a mouseover on the variable to see its value.

Alex
 
Upvote 0
Norie, the workbook will have 3 pages initially as follows:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
1)"INFO" - Basic data entered, no formulas or buttons (Project & contact info)<o:p></o:p>
<o:p></o:p>
2)"PCO LOG" - the "Main" worksheet, used for entering data about PCO's (Pricing, Dates, PCO Status).<o:p></o:p>
<o:p></o:p>
On this sheet all PCO's are listed in a descending fashion, one bellow the next with related information listed to the right in the same row. Because of the need to revise these PCOs each PCO will be followed by revisions below them before moving on to the next PCO# <o:p></o:p>
Example: <o:p></o:p>
row16= PCO1 <o:p></o:p>
row17=PCO1 Rev1<o:p></o:p>
row18=PCO1 Rev2<o:p></o:p>
row19=PCO1 Rev3<o:p></o:p>
row20=PCO1 Rev4<o:p></o:p>
row21=PCO1 Rev5<o:p></o:p>
row22=PCO2<o:p></o:p>
All revision rows will be initially hidden until needed (unhidden when associated worksheet is created by button use)<o:p></o:p>
<o:p></o:p>
The buttons are located on the left side of each PCO row (one for each). Pressing the button copies the "Template" worksheet and renames it to the "PCO" it is associated with. (Example: button next to PCO1 creates worksheet "PCO1". If the button is pressed again it will do two things: Copy "Template" worksheet, rename it to "PCO1 Rev1" and secondly unhide the row on the worksheet "PCO LOG" associated with "PCO1 Rev1".<o:p></o:p>
<o:p></o:p>
3)"Template" - A single page contract intended to be printed signed and faxed for approval. It pulls basic project and contact information from the "INFO" worksheet. It contains an area for text input for PCO details, pricing and terms. No information will be pulled from this worksheet back to "PCO LOG" worksheet.<o:p></o:p>
<o:p></o:p>
Other Details: I'm going to preset links to all possible PCO worksheets including the revision worksheets (links on "PCO LOG" worksheet). Initially the links won't work because the targeted page wont exist, once the pages are generated the links will work. All PCO and Rev worksheets will have a link back to the "PCO LOG" worksheet.<o:p></o:p>
<o:p></o:p>
The spreadsheet design/construction is going well, I'm just trying to hammer out the VBA which I am still learning (Still entry level).

<o:p>Thanks</o:p>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top