How do you disable or delete a button in VBA?

[grimlock]

How do you disable or delete a button in VBA code after the button is pressed?

Thanks.

 

[grimlock]

Thanks Alexander,
Initially P = "L"
After I made your suggested change P=""
It passes the line it previously stopped on.

It then hangs on the line:
If a(1) > 4 Then
I checked in the VBA Editor and I got: a(1)=""

 

[xenou]

Ah, I see...Its hung up on the PCO Log sheet...
Hang on a sec...

 

[xenou]

Oddly similar to another recent post.

How about a change of plan? Say we work out:

1) the macro to unhide the row for the next sheet you need...

Then:
2) simply double-click the cell with the "new" sheet name in it, and a sheet with that name will be created? (I.e, if you click PCO1 rev2, you get a new sheet PCO1 rev2)

Or:

3) unhide the row and take the value in the sheet name cell and create that sheet all in one shot - ie. having the sheet names there really makes that part easy -- we can just read the new sheet name from the cell.

 

[xenou]

EDIT:
Never mind. waiting on the answer to the above. I was going to fix the glitch but really the logic is wrong for the revision numbering anyway...

 

[xenou]

Where are the code and the button located?

That's a good question...where is the button?

 

[Fazza]

I have only perused the thread, so I may be off target.

What about something like - untested & from memory - wksNew.shapes(1).delete

Or something like that. If there are multiple shapes, loop through them and find the one that you want to delete. I guess looking at its name.

HTH, Fazza

 

[grimlock]

I think if we leave the "Rev" rows hidden until the revision is needed would be best. I'm concerned about confusing some of the users, many won't have much excel experience. Buttons are easy to understand for most.

Reading the names from the cells to name the new worksheets would be ok, I didn't think of that before, maybe that would make things easier.

I was previously thinking of having one button per PCO set that generates the different Worksheets as needed. Then just copying it and altering it to work on the next PCO set. I guess there could be two buttons per set, one to generate the first "PCO" and then a button to generate the "Revisions" and unhide the rows.

If having a hyperlink in the cells do all the work would be a better solution I could add one in each row that when clicked on would unhide the next row and create the new Worksheet. I think the buttons would be cleaner but in the end I just want it to be functional.

Thanks

EDIT: see post #10 I wrote some greater detail on the pages in the workbook, all buttons are on the "PCO LOG" worksheet:
sheet 1 "INFO"
sheet 2 "PCO LOG"
sheet 3 "Template"

 

[xenou]

Okay, I have to say I'm not a fan of a lot of buttons...they can be hard to size and keep in place (notwithstanding you can set them to "not move or size with cells").

I'd suggest using a double-click event, and place a small tag in the cell next to your PCO names. We'll read the sheet names from your pre-entered list up to 100, create the next sheet by checking for which one should come next (a new PCO sheet or a revision). And we'll unhide the cell with that sheet's name.

Does that sound workable? In other words, double-click on A16, and it will create a new PCO1 sheet, or the next revision as needed. A17 and following may or may not be hidden.

Here's my test sheet:

book1.xls
	A	B	C	D
16	DoubleClickForNewPCO1Sheet	PCO1
17		PCO1rev1
18		PCO1rev2
19		PCO1rev3
20		PCO1rev4
21		PCO1rev5
22	DoubleClickForNewPCO2Sheet	PCO2
23		PCO2rev1
24		PCO2rev2
25		PCO2rev3
26		PCO2rev4
27		PCO2rev5
28	DoubleClickForNewPCO3Sheet	PCO3
29		PCO3rev1
30		PCO3rev2
31		PCO3rev3
32		PCO3rev4
33		PCO3rev5
Sheet1

And Test Code:
(WORKSHEET CODE - goes in the sheet code pane, after right-clicking on the sheet tab).

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'-------------------------------------------------------------------------------
'Purpose:
'When a cell in column A is double-clicked,
'    to add a sheet based on the cell value to the right in Column B.
'
'Sub will first see if the main PCO sheet exists.
'    If not, it creates it.
'    If so, it looks for the next revision sheets until it
'        finds the next revision sheet to be made.
'
'The double-click event is triggered only by
'    clicking in a cell in column A, after row 15 and before row 1000
'    and there should be no cell to the right with the words "rev" in it.
'
'-------------------------------------------------------------------------------
If Not Intersect(Target, Range("A16:A1000")) Is Nothing Then
    '//second check - only cells to the left of main PCO numbers, not revisions
    If Not InStr(1, Target.Offset(0, 1).Value, "rev", vbTextCompare) Then
        Cancel = True
        Call CreateNextPCOSheet(Target)
        Me.Activate
        Target.Select
    End If
End If

End Sub
'-----------------------------------------
Sub CreateNextPCOSheet(ByRef rngClicked As Range)
Dim ws As Worksheet
Dim rng As Range
Dim a
Dim i As Long
Dim blnSheetMade As Boolean

    '//values for PCO and 5 revisions - sheet names
    Set rng = rngClicked.Offset(0, 1).Resize(6, 1)
    If WorksheetFunction.CountA(rng)<> 6 Then
        MsgBox "An error occurred creating the next PCO sheet: Excel cannot find the sheet names."
        Exit Sub
    End If
    a = rng.Value
    
    '//check if these sheets exist in turn
    For i = 1 To UBound(a)
        
        If Not MySheetExists(a(i, 1)) Then
                   
            '//Sheet doesn't exist.  Create one.
            Worksheets("Template").Copy After:=Worksheets(Worksheets.Count)
            ActiveSheet.Name = a(i, 1)
            
            '//unhide row for revision
            rng.Cells(i).EntireRow.Hidden = False
            blnSheetMade = True
            Exit For
        
        End If
    
    Next i
    
    '//Oops.  Already have all 5 revisions
    If blnSheetMade = False Then
        MsgBox "Only 5 revisions sheets are available."
    End If

End Sub
'-----------------------------------------
Private Function MySheetExists(ByVal strSheetName As String)
Dim s As String
On Error Resume Next
s = ThisWorkbook.Worksheets(strSheetName).Name
If Err Then
    MySheetExists = False
Else
    MySheetExists = True
End If
End Function

 

[grimlock]

It works!!!
It's amazing how simple the final solution was compared to my original idea of 600 buttons... (laughing at self)

Thank you Alexander,
I've learned a great deal from what you have helped me with these last few days. I'm going to continue to study VBA and hopefully be able to help others as well someday.

Thanks again!

 

[xenou]

You bet. I was much happier with my second go at this myself... Sometimes really I think its better to think it through on paper, but I hardly ever do that...usually just start going and find out later what kind of corners I've painted myself into {sigh}.