VBA: Rename File When Excel is still/Keep Opening
-
- 3StarLounger
- Posts: 243
- Joined: 17 Feb 2022, 05:16
VBA: Rename File When Excel is still/Keep Opening
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
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
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA: Rename File When Excel is still/Keep Opening
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
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 243
- Joined: 17 Feb 2022, 05:16
Re: VBA: Rename File When Excel is still/Keep Opening
hi hans, thank but not work
i have write code complete like this:
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
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA: Rename File When Excel is still/Keep Opening
That won't work indeed. Did you try the code as posted by me?
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 243
- Joined: 17 Feb 2022, 05:16
Re: VBA: Rename File When Excel is still/Keep Opening
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
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
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA: Rename File When Excel is still/Keep Opening
Did you copy the code into the ThisWorkbook module, as I mentioned in my first reply?
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 243
- Joined: 17 Feb 2022, 05:16
Re: VBA: Rename File When Excel is still/Keep Opening
i have do hans, but show message syntax error,
"Private WithEvents app As Application"
"Private WithEvents app As Application"
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 12631
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: VBA: Rename File When Excel is still/Keep Opening
You have added the line
at the beginning of this. That is causing the error. Just delete this extra line
Code: Select all
Sub rename()
StuartR
-
- 3StarLounger
- Posts: 243
- Joined: 17 Feb 2022, 05:16
Re: VBA: Rename File When Excel is still/Keep Opening
still not work .
code Private WithEvents app As Application is always show error (red font)
code Private WithEvents app As Application is always show error (red font)
-
- Administrator
- Posts: 12631
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: VBA: Rename File When Excel is still/Keep Opening
Can you please try a test for me.
Remove absolutely ALL code from This Workbook except for
Then compile the project and see if you still get an error.
Your screen should look like this...
Remove absolutely ALL code from This Workbook except for
Code: Select all
Private WithEvents app as Application
Your screen should look like this...
You do not have the required permissions to view the files attached to this post.
StuartR
-
- 3StarLounger
- Posts: 243
- Joined: 17 Feb 2022, 05:16
Re: VBA: Rename File When Excel is still/Keep Opening
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?
how to run the code
usually i have use
sub rename()
....
..
end sub
not like this?
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA: Rename File When Excel is still/Keep Opening
I have attached a sample add-in, zipped. See if you can unzip and install it.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 12631
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: VBA: Rename File When Excel is still/Keep Opening
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.
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_WorkbookOpenSusanto3311 wrote: ↑24 Sep 2022, 08:51how to run the code
usually i have use
sub rename()
....
..
end sub
StuartR
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA: Rename File When Excel is still/Keep Opening
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.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 243
- Joined: 17 Feb 2022, 05:16
Re: VBA: Rename File When Excel is still/Keep Opening
hi hans, how to use it? how to use to renaming file..
i confuse it
i confuse it
You do not have the required permissions to view the files attached to this post.
-
- 3StarLounger
- Posts: 243
- Joined: 17 Feb 2022, 05:16
Re: VBA: Rename File When Excel is still/Keep Opening
please, how to step to do with the code.
how to use the code to renaming file when the file (excel) is opening/used
how to use the code to renaming file when the file (excel) is opening/used
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA: Rename File When Excel is still/Keep Opening
Have you tried opening a workbook after installing the add-in? You should get a dialog.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 243
- Joined: 17 Feb 2022, 05:16
Re: VBA: Rename File When Excel is still/Keep Opening
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.
i can't see the dialog is showing, when i open new file , that's nothing different.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA: Rename File When Excel is still/Keep Opening
If you don't see the Save As dialog when you open a workbook, I can't help you.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 243
- Joined: 17 Feb 2022, 05:16
Re: VBA: Rename File When Excel is still/Keep Opening
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
i don't know why the code not work for me..
i have use Excel 2021, thank you for your time