commandbutton removal

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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
 
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
 
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
 
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
 
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
 
Back
Top