Help to a code - Save as - Debug

[gnaske]

First I'm sorry the project is in Danish, but I hope you'll understand anyway.

I had made my own Ribbon in the project I'm working at and I get a failure, when I use the code I use in Save as new name (Gem som nyt navn), when I select an already existing file and click No at the Saving Popup.

Picture below: Save as new name in my Ribbon.

http://i7.tagstat.com/image09/5/df98/00hI054Zt-o.jpg


I click at Save as new name in the Ribbon.
Select an already existing file name.
And click No at the popup, which say the file already exist.

http://i4.tagstat.com/image09/5/df98/00hH054Zt-o.jpg


Then I get this failure.

http://i5.tagstat.com/image09/5/df98/00hG054Zt-o.jpg

I want to force the Save as new name to save the file as an xlsm file

http://i6.tagstat.com/image09/5/df98/00hF054Zt-o.jpg


You can open and close the Excel Menu too, using the Ribbon.
The Password is 123

http://i2.tagstat.com/image09/5/df98/00hJ054Zt-o.jpg


I hope someone can help with this failure.
The project is attached.

Thank you in advance.

Ib

[Kenneth Hobs]

Set the variable type as Variant. Check for False, not "False". Use MsgBox or Debug.Print or just Debug, F8, to see what the value is when the dialog is closed without selection.

[gnaske]

Hmmm.

I'm not that good in VBA coding and learning by trying.
I'm not sure I understand what you tell !

Here is the code.
Try to change it the way you think it will work.

The Code is also located in Modules - CallBackRibbon - Button18_onAction (control as IRibbonControl)
And in Forms - A1_Hoved_Menu - CommandButton21_Click()


Public Sub Button18_onAction(control As IRibbonControl)
' Gem som nyt navn
'
Dim varWorkbookName As String
Dim sFileExtension As String

Application.EnableEvents = False

varWorkbookName = Application.GetSaveAsFilename(InitialFileName:="", _
filefilter:="Excel Macro Enabled Workbook (*.xlsm), *.xlsm)", _
FilterIndex:=1)


If varWorkbookName <> "False" Then
sFileExtension = CreateObject("Scripting.FileSystemObject").GetExtensionName(varWorkbookName)
varWorkbookName = Left(varWorkbookName, Len(varWorkbookName) - Len(sFileExtension)) & "xlsm"

ActiveWorkbook.SaveAs Filename:=varWorkbookName, FileFormat:=52
End If

Application.EnableEvents = True
End Sub

[Kenneth Hobs]

Dim varWorkbookName As Variant

[gnaske]

Now I had tried to Dim varWorkbookName as variant, but the failure is in the red line below.

I'm sorry, I'm not sure I understand what you're telling !


Public Sub Button18_onAction(control As IRibbonControl)
' Gem som nyt navn
'
Dim varWorkbookName As Variant
Dim sFileExtension As String

Application.EnableEvents = False

varWorkbookName = Application.GetSaveAsFilename(InitialFileName:="", _
filefilter:="Excel Macro Enabled Workbook (*.xlsm), *.xlsm)", _
FilterIndex:=1)


If varWorkbookName <> "False" Then
sFileExtension = CreateObject("Scripting.FileSystemObject").GetExtensionName(varWorkbookName)
varWorkbookName = Left(varWorkbookName, Len(varWorkbookName) - Len(sFileExtension)) & "xlsm"

ActiveWorkbook.SaveAs Filename:=varWorkbookName, FileFormat:=52
End If

Application.EnableEvents = True
End Sub

[Kenneth Hobs]

What does this show in the Immediate Window after your put it before that line and play it? You must have a valid filename.

Debug.Print varWorkbookName

[gnaske]

I have two different codes.
They both work when I Save as and point at an already existing file, as far as I don't regret and say NO, after I pointed at the file.

The failure is in the same line in both codes..


Public Sub Button18_onAction(control As IRibbonControl)
' Gem som nyt navn
'
Dim varWorkbookName As Variant
Dim sFileExtension As String

Application.EnableEvents = False

varWorkbookName = Application.GetSaveAsFilename(InitialFileName:="", _
filefilter:="Excel Macro Enabled Workbook (*.xlsm), *.xlsm)", _
FilterIndex:=1)


If varWorkbookName <> "False" Then
sFileExtension = CreateObject("Scripting.FileSystemObject").GetExtensionName(varWorkbookName)
varWorkbookName = Left(varWorkbookName, Len(varWorkbookName) - Len(sFileExtension)) & "xlsm"

ActiveWorkbook.SaveAs Filename:=varWorkbookName, FileFormat:=52
End If

Application.EnableEvents = True
End Sub
Private Sub CommandButton21_Click()
Unload Me
Dim file_name As Variant
' Get the file name.
file_name = Application.GetSaveAsFilename( _
filefilter:="Excel Files,*.xlsm,All Files,*.*", _
Title:="Save As File Name")

' See if the user canceled.
If file_name = False Then Exit Sub

' Save the file with the new name.
If LCase$(Right$(file_name, 4)) <> ".xlsm" Then
file_name = file_name ' & ".xlsm"
End If
ActiveWorkbook.SaveAs Filename:=varWorkbookName, FileFormat:=52
End Sub

[Bob Phillips]

Public Sub Button18_onAction(control As IRibbonControl)
' Gem som nyt navn
'
Dim varWorkbookName As String
Dim sFileExtension As String
Dim ans As VbMsgBoxResult

Application.EnableEvents = False

varWorkbookName = Application.GetSaveAsFilename(InitialFileName:="", _
filefilter:="Excel Macro Enabled Workbook (*.xlsm), *.xlsm)", _
FilterIndex:=1)

If varWorkbookName <> "False" Then
sFileExtension = CreateObject("Scripting.FileSystemObject").GetExtensionName(varWorkbookName)
varWorkbookName = Left(varWorkbookName, Len(varWorkbookName) - Len(sFileExtension)) & "xlsm"

If Dir(varWorkbookName) <> "" Then

ans = MsgBox("A file named " & varWorkbookName & " alreadys exists in this location. Do you want to replace it?", _
vbYesNoCancel + vbInformation, "Gem som nyt navn")
If ans = vbYes Then

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=varWorkbookName, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
ConflictResolution:=xlLocalSessionChanges
Application.DisplayAlerts = True
End If
End If
End If

Application.EnableEvents = True
End Sub

[gnaske]

Hi xld.

Thank you.

I'm sorry, but I haven't been able to go online in the forum for a couple of days.

The code is now working without the bug, if I click at No after Save as new name, but unfortunately your code doesn't save the file at all, if I just want to Save as.

As I wrote earlier, I'm not very good with VBA codes and had tried to find some working codes using Google and either they didn't saved the file or the bug came, when I clicked No.
I had tried to combine some codes too, but with the same result.

I really appreciate your help and hope you'll continue to try to solve this problem for me.

Thank you in advance.

Ib

[Bob Phillips]

Ah yes, sorry I missed a bit

Public Sub Button18_onAction(control As IRibbonControl)
' Gem som nyt navn
'
Dim varWorkbookName As String
Dim sFileExtension As String
Dim ans As VbMsgBoxResult

Application.EnableEvents = False

varWorkbookName = Application.GetSaveAsFilename(InitialFileName:="", _
filefilter:="Excel Macro Enabled Workbook (*.xlsm), *.xlsm)", _
FilterIndex:=1)

If varWorkbookName <> "False" Then
sFileExtension = CreateObject("Scripting.FileSystemObject").GetExtensionName(varWorkbookName)
varWorkbookName = Left(varWorkbookName, Len(varWorkbookName) - Len(sFileExtension)) & "xlsm"

If Dir(varWorkbookName) <> "" Then

ans = MsgBox("A file named " & varWorkbookName & " alreadys exists in this location. Do you want to replace it?", _
vbYesNoCancel + vbInformation, "Gem som nyt navn")
If ans = vbYes Then

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=varWorkbookName, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.DisplayAlerts = True
End If
Else

ActiveWorkbook.SaveAs Filename:=varWorkbookName, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
End If
End If

Application.EnableEvents = True
End Sub

[gnaske]

Wouuw, thank you xld - Now it works :bow:

It has given me a lot of trouble for a while, trying to combine multiple code found using Google.

Thank you very very much :clap:

Kind regards.

Ib

[gnaske]

xld.

I guess you know a lot about coding and have a request.

When I want to edit in the project I can show Excel's menu, using a Password.
And hide it again, after editing.

http://i2.tagstat.com/image09/5/df98/00hJ054Zt-o.jpg


As you can see in the pictures below I had made a simple UserForm and code to do that.
The Password is Bus right now.

http://i3.tagstat.com/image09/5/df98/00hM054Zt-o.jpg

http://i1.tagstat.com/image09/5/df98/00hK054Zt-o.jpg


I had been thinking about an easy way, for other Admins at the project, to change the Password to their own.

http://i0.tagstat.com/image09/5/df98/00hL054Zt-o.jpg


Change Password code should be something as:
- If Old Pass <> the present Pass - Msgbox "Wrong Pass".
- If Old Pass = the present Pass, be able to write a New Pass.
- If Repeat Pass <> the New Pass - Msgbox "Passwords is not equal".
- If the two New Pass is okay, insert the new Pass and jump back to the UserForm Password.

Do you think it's possible to make a code like that ?

The project is attached too.

Ib