Is it possible that a button in a form deletes the form?

  • Thread starter Thread starter Mr. Autumn
  • Start date Start date
M

Mr. Autumn

I'd like to know if it is possible for a form in a workbook to have a
command button whose click action deletes the form and saves the workbook
(with the same or different name) without the form.

Thanks in advance.
 
Mr. Autumn,

That is possible. Personally I don't like VBA code to delete things from my
workbooks. I would prefer a solution that changes the value of a certain
variable, so that the form won't show up anymore. You will need to check for
the value of the variable on every form-call, but at least it won't do
anything to your spreadsheet that cannot be reversed...

You could place the value for the variable in a cell somewhere (maybe on a
hidden sheet) or you could create a fictive range name with the value. Or
you could have a click on the button have VBA add a line of code to your
macro... I hope you know how to do the first two solutions. If you prefer to
use the third one, or you really want to have the form deleted, please let
me know. If you haven't got a clue of what I'm talking about, please let me
know and I'll explain...

Kind regards,

Marcel Kreijne
Quandan - Steunpunt voor spreadsheetgebruikers
(Quandan - dutch supportsite for spreadsheetusers)
www.quandan.nl
 
Chip Pearson has lots of notes at for programming to the VBE at:

http://www.cpearson.com/excel/vbe.htm

but one way to get rid of a userform (named userform1) is:

Behind the button:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
Application.OnTime Now + TimeSerial(0, 0, 5), "deleteme"
End Sub

And in a general module:

Option Explicit
Sub deleteme()

If ActiveWorkbook.VBProject.Protection Then
MsgBox "Protected project" & vbLf & "Cannot complete"
Else
With ThisWorkbook.VBProject.VBComponents
.Remove .Item("userform1")
End With
'and then
'thisworkbook.saveas filename:=....
End If
End Sub

And to test it:
Sub testme()
UserForm1.Show
End Sub

You can record a macro to get the thisworkbook.saveas syntax correct.

Or even
thisworkbook.save
to reuse the name.
 
Back
Top