Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
OnutzaGirl pe Simpatie
 | Femeie
24 ani
Galati
cauta Barbat
24 - 51 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 787
|
|
Today I encountered a problem with VBA macro enabled files backed up on OneDrive.
Here's a description of the problem:
I have an Excel xlsm file, let's call it April, containing a macro that is assigned to a shape taking the role of a button.
The file is located on the PC, but the folder where it is located is also backed up on OneDrive.
If I save the file as a copy, let's call it May, the macro allocated to the button remains in the April file.
If both files are closed, then April is renamed to say April 2024, when opening May it will generate an error when trying to run the macro since it can't find the file named April anymore.
Putting the following macro into ThisWorkbook fixes the problem as it assigns each shape in my workbook the correct macro from the file that is opened:
Private Sub Workbook_Open()
Dim ws As Worksheet
Dim shp As Shape
' Loop through each worksheet in the workbook
For Each ws In ThisWorkbook.Sheets
' Loop through each shape in the worksheet
For Each shp In ws.Shapes
' Check if the shape is an ActiveX control (like a button)
If shp.Type = msoOLEControlObject Then
' Check if the control is a CommandButton
If shp.OLEFormat.Object.progID = "Forms.CommandButton.1" Then
' Assign the macro "GetPDF" to the button
shp.OnAction = "Gen_PDF" ' or:
'shp.OnAction = "'" & ThisWorkbook.Name & "'!Gen_PDF"
End If
ElseIf shp.Type = msoFormControl Then
' Check if the shape is a form control button
If shp.FormControlType = xlButtonControl Then
' Assign the macro "GetPDF" to the form control button
shp.OnAction = "Gen_PDF" ' or:
'shp.OnAction = "'" & ThisWorkbook.Name & "'!Gen_PDF"
End If
End If
Next shp
Next ws
End Sub
|
If you name the form in every sheet as: GenPDF then the macro changes to:
Private Sub Workbook_Open()
Dim ws As Worksheet
Dim shp As Shape
' Loop through each worksheet in the workbook
For Each ws In ThisWorkbook.Sheets
' Loop through each shape in the worksheet
For Each shp In ws.Shapes
' Check if the shape's name is "GenPDF"
If shp.Name = "GenPDF" Then
' Assign the macro "Gen_PDF" to the shape
shp.OnAction = "Gen_PDF" ' or:
'shp.OnAction = "'" & ThisWorkbook.Name & "'!Gen_PDF"
End If
Next shp
Next ws
End Sub |
Source: ChatGPT
|
|
| pus acum 6 luni |
|