View Full Version : Help to a code - Save as - Debug
gnaske
02-12-2013, 12:50 PM
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
02-12-2013, 01:08 PM
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
02-12-2013, 01:19 PM
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
02-12-2013, 01:24 PM
Dim varWorkbookName As Variant
gnaske
02-12-2013, 01:36 PM
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
02-12-2013, 01:42 PM
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
02-12-2013, 01:51 PM
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
02-13-2013, 02:57 AM
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
02-15-2013, 12:45 AM
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
02-15-2013, 02:40 AM
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
02-15-2013, 02:51 AM
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
02-15-2013, 03:48 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.