commandbutton removal

[Guest]

Please help,

When i do a saveas and sendmail; the new file contains the original command
button and the macro associated with the button, I want to remove to button
in the newly copied file during the saveas function. Any ideas?

 

[Guest]

hi
you problem comes from the saveas that you are doing. this saves the file
under a different name along with the data , controls, macros and all else.
what you need to do is just save the range of data to a new workbook and
email that.
how?
paste this in a standard module. you can fire it from a worksheet button or
a toolbar icon.
Sub mac1SaveRange()

'Macro written by FSt1 4/27/00

Dim cnt As Long
Dim cell As Range
On Error GoTo err1

MsgBox "You have selected range" & Selection.Address
If Selection.Cells.Count = 1 Then
If MsgBox("You have selected only one cell. Continue?", vbYesNo,
"Warning") = vbNo Then
Exit Sub
End If
End If
cnt = 0
For Each cell In Selection
If Not IsEmpty(cell) Then
cnt = cnt + 1
End If
Next
If cnt = 0 Then
If MsgBox("There is no data in the selected range. Continue?!?!?!?!?",
vbYesNo, "Warning") = vbNo Then
Exit Sub
End If
End If
'ActiveSheet.UsedRange.Select
Selection.Copy
Workbooks.Add
Range("A1").PasteSpecial xlPasteAll
Application.Dialogs(xlDialogSaveAs).Show
err1:
MsgBox ("Need a range to save.")
Exit Sub
End Sub

Post back if you have questions
Regards
FSt1

 

[Guest]

Thanks,

This is my code:

Private Sub CommandButton1_Click()



'**********************************************************************************
'Working in 97-2007

'**********************************************************************************
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String



With Application
.ScreenUpdating = False
.EnableEvents = False
End With



Set Sourcewb = ActiveWorkbook



'**********************************************************************************
'Copy the sheet to a new workbook

'**********************************************************************************
ActiveSheet.Copy
Set Destwb = ActiveWorkbook



'**********************************************************************************
'Determine the Excel version and file extension/format

'**********************************************************************************
With Destwb
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else

'You use Excel 2007
'We exit the sub when your answer is NO in the security dialog
that you only
'see when you copy a sheet from a xlsm file with macro's
disabled.

If Sourcewb.Name = .Name Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your answer is NO in the security dialog"
Exit Sub
Else
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End If
End With




'**********************************************************************************
'Change all cells in the worksheet to values if you want
With Destwb.Sheets(1).UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select

End With
Application.CutCopyMode = False

'**********************************************************************************



'**********************************************************************************
'Save the new workbook/Mail it/Delete it

'**********************************************************************************
TempFilePath = Environ$("temp") & "\"
TempFileName = "Die_ " & Sourcewb.Name & " " & Format(Now, "dd-mmm-yy
h-mm-ss")



With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
.SendMail "KIP WISEMAN", _
"This is the RFQ_ " & Sourcewb.Name
On Error GoTo 0
.Close SaveChanges:=False
End With



'**********************************************************************************
'Delete the file you have send

'**********************************************************************************
Kill TempFilePath & TempFileName & FileExtStr
With Application
.ScreenUpdating = True
.EnableEvents = True
End With


End Sub

My issue is that the button used to launch this code needs to be removed
once executed. I want the new file to NOT have the code or embedded
commandbutton. the rest of the function works well. Is this possible?Thanks
again

 

[Guest]

hi again,
very good. you did you homework well. i'm impressed.
i noticed in your code...
Set Sourcewb = ActiveWorkbook
'************************
'Copy the sheet to a new workbook
'*************************
ActiveSheet.Copy
Set Destwb = ActiveWorkbook
....but did not add a workbook. seems now both Sourcewb and Destwb are set to
the same and only activeworkbook.

is this an opps?

Post back if i got it all wrong.
regards
FSt1

 

[Guest]

thanks again for the reply,

The source and destination are indeed the same, maybe why I am having the
issue. The idea is to duplicate the worksheet into a new workbook and the
send the new copy off via outlook e-mail while deleting the temp file. The
functionality works well, but I have a command button on the source that is
copied to the dest and the code is still on the back end! I have tried to
make the command button "not visible" but it disappears on the source
worksheet also. HMmmmm!

thanks for the assistance.

Kip

 

[Guest]

hi again,
you don't have a new workbook. you are making a copy of the source workbook.
use workbooks.add then paste the data in it. then you will have a new
workbook without buttons and code.

regards
FSt1