frank_m
10-27-2010, 02:30 AM
I have two command buttons on a sheet.
The first saves the workbook as a shared. (that works fine)
Private Sub CommandButton1_Click()
Dim wb As Workbook
Dim fileSaveName As String
Dim InitFileName As String
Dim strDate As String
strDate = Format(Now, "dd-mmm-yy h-mm-ss")
InitFileName = "C:\Users\Owner\Desktop\SharedTest" & Range("I4").Value & "_" & strDate
Set wb = ActiveWorkbook
fileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitFileName, _
filefilter:="Excel files , *.xls")
With wb
If fileSaveName <> "False" Then
.SaveAs fileSaveName, , , , , , xlShared
.KeepChangeHistory = True
.HighlightChangesOptions When:=xlAllChanges
.ListChangesOnNewSheet = True
.HighlightChangesOnScreen = False
.Close False
Else
.Close False
Exit Sub
End If
End With
End Sub Using CommandButton2 shown below I was attempting to save it back to not being shared and by a new name. However, the code does not accomplish the un-sharing. It merely saves it as another shared workbook.
Is it possible to unshare a workbook via a preexisting Command button within the shared file?
Private Sub CommandButton2_Click()
Dim wb As Workbook
Dim fileSaveName As String
Dim InitFileName As String
Dim strDate As String
strDate = Format(Now, "dd-mmm-yy h-mm-ss")
InitFileName = "C:\Users\Owner\Desktop\RemoveSharedTest" & Range("I4").Value & "_" & strDate
Set wb = ActiveWorkbook
fileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitFileName, _
filefilter:="Excel files , *.xls")
With wb
If fileSaveName <> "False" Then
.SaveAs fileSaveName
.Close
Else
.Close False
Exit Sub
End If
End With
End Sub
The first saves the workbook as a shared. (that works fine)
Private Sub CommandButton1_Click()
Dim wb As Workbook
Dim fileSaveName As String
Dim InitFileName As String
Dim strDate As String
strDate = Format(Now, "dd-mmm-yy h-mm-ss")
InitFileName = "C:\Users\Owner\Desktop\SharedTest" & Range("I4").Value & "_" & strDate
Set wb = ActiveWorkbook
fileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitFileName, _
filefilter:="Excel files , *.xls")
With wb
If fileSaveName <> "False" Then
.SaveAs fileSaveName, , , , , , xlShared
.KeepChangeHistory = True
.HighlightChangesOptions When:=xlAllChanges
.ListChangesOnNewSheet = True
.HighlightChangesOnScreen = False
.Close False
Else
.Close False
Exit Sub
End If
End With
End Sub Using CommandButton2 shown below I was attempting to save it back to not being shared and by a new name. However, the code does not accomplish the un-sharing. It merely saves it as another shared workbook.
Is it possible to unshare a workbook via a preexisting Command button within the shared file?
Private Sub CommandButton2_Click()
Dim wb As Workbook
Dim fileSaveName As String
Dim InitFileName As String
Dim strDate As String
strDate = Format(Now, "dd-mmm-yy h-mm-ss")
InitFileName = "C:\Users\Owner\Desktop\RemoveSharedTest" & Range("I4").Value & "_" & strDate
Set wb = ActiveWorkbook
fileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitFileName, _
filefilter:="Excel files , *.xls")
With wb
If fileSaveName <> "False" Then
.SaveAs fileSaveName
.Close
Else
.Close False
Exit Sub
End If
End With
End Sub