VBA: Rename File When Excel is still/Keep Opening

[Susanto3311]

hi all..

i'm looking for code to rename excel file when excel/worksheet is opening (standbye)
this step :
1. click code , show message box "type your new name file...."
2. ok/done.
the code can make add-ins excel.

anyone help, thank you in advance.

susant

[HansV]

Copy the following code into the ThisWorkbook module of your add-in:

Code: Select all

Private WithEvents app As Application

Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
    Application.Dialogs(xlDialogSaveAs).Show
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Set app = Nothing
End Sub

Private Sub Workbook_Open()
    Set app = Application
End Sub

[Susanto3311]

hi hans, thank but not work
i have write code complete like this:

Code: Select all

Sub rename()
Private WithEvents app As Application

Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
    Application.Dialogs(xlDialogSaveAs).Show
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Set app = Nothing
End Sub

Private Sub Workbook_Open()
    Set app = Application
End Sub

[HansV]

That won't work indeed. Did you try the code as posted by me?

[Susanto3311]

of course hans, show message error:
compile error!! only valid in object module
the code:
'Private WithEvents app As Application' is error (red color).
i have you code to paste into Module

[HansV]

Did you copy the code into the ThisWorkbook module, as I mentioned in my first reply?

[Susanto3311]

i have do hans, but show message syntax error,
"Private WithEvents app As Application"

[StuartR]

You have added the line

Code: Select all

Sub rename()

at the beginning of this. That is causing the error. Just delete this extra line

[Susanto3311]

still not work .
code Private WithEvents app As Application is always show error (red font)

[StuartR]

Can you please try a test for me.

Remove absolutely ALL code from This Workbook except for

Code: Select all

Private WithEvents app as Application

Then compile the project and see if you still get an error.

Your screen should look like this...

[Susanto3311]

hi stuart, nothing getting error, but i can't see the code work well.
how to run the code
usually i have use
sub rename()
....
..
end sub

not like this?

[HansV]

I have attached a sample add-in, zipped. See if you can unzip and install it.

MyAddin.zip

[StuartR]

Your screenshot doesn't show what module this code is in. Can you get a wider view so we can see that information. It is absolutely essential that this is in the ThisWorkbook module of the correct workbook.

how to run the code
usually i have use
sub rename()
....
..
end sub

There are three separate Sub statements in the code that Hans posted. You don't need Sub rename because the rename happens in Sub app_WorkbookOpen

[HansV]

how to run the code

This is not a macro that you run manually. The code consists of so-called event procedures that run automatically when specific events occur, such as the user opening a workbook.

In the add-in that I attached to my previous reply, I added code to prevent the Save As dialog to be displayed for add-ins and the user's personal macro workbook.

[Susanto3311]

hi hans, how to use it? how to use to renaming file..
i confuse it

[Susanto3311]

please, how to step to do with the code.
how to use the code to renaming file when the file (excel) is opening/used

[HansV]

Have you tried opening a workbook after installing the add-in? You should get a dialog.

[Susanto3311]

i have do that your suggestion but i can't see what change..
i can't see the dialog is showing, when i open new file , that's nothing different.

[HansV]

If you don't see the Save As dialog when you open a workbook, I can't help you.

[Susanto3311]

i think i have the problem with my excel.
i don't know why the code not work for me..
i have use Excel 2021, thank you for your time