Hello,
I have updated the codes and want to replace the codes in userforms along with replacement of userforms on the existing excel file.
i. As the form have been already distributed and under usage there will be data/record in the sheet("Data") which should not be disturbed.
ii. Also would like to replace the codes in the sheets.
My requirement is:
a. I need to add a code to open the user file thro' file location by search window and select the file to use as string as the target file to update/patch.
b. With the below code, When I reclick the button the target UserForm(rvcform) is deleted.
c. How to modify and apply the code properly so that by sending the file(update, enduser should click and select the file and replace/update the code.
d. After update both the files should be saved and closed.
e. Target file worksheets and VB codes are protected.
From my understanding that the worksheet can be unprotected and will automatically protected during reopening but, the vbcode/modules cannot be unlocked & update & relocked thro vba (I have the unlock password, as i have created)
Used various methods references from forums but still not successful
From forum reference I am using the following code:
I have updated the codes and want to replace the codes in userforms along with replacement of userforms on the existing excel file.
i. As the form have been already distributed and under usage there will be data/record in the sheet("Data") which should not be disturbed.
ii. Also would like to replace the codes in the sheets.
My requirement is:
a. I need to add a code to open the user file thro' file location by search window and select the file to use as string as the target file to update/patch.
b. With the below code, When I reclick the button the target UserForm(rvcform) is deleted.
c. How to modify and apply the code properly so that by sending the file(update, enduser should click and select the file and replace/update the code.
d. After update both the files should be saved and closed.
e. Target file worksheets and VB codes are protected.
From my understanding that the worksheet can be unprotected and will automatically protected during reopening but, the vbcode/modules cannot be unlocked & update & relocked thro vba (I have the unlock password, as i have created)
Used various methods references from forums but still not successful
From forum reference I am using the following code:
Code:
Sub UpdateUserBook()Dim FileName
FileName = "[COLOR=#ff0000]recordl.xlsm[/COLOR]" ' [COLOR=#ff0000]need here browse and selected file name[/COLOR]
' Activate workboook
On Error Resume Next
Workbooks(FileName).Activate
rvcform.cmdClose = True
Call Workbook_open
'On Error GoTo booboo
unpass = "xxxx"
For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Unprotect Password:=unpass
Next
'Exit Sub
'booboo: MsgBox "There is s problem - check your password, capslock, etc."""
If Err <> 0 Then
MsgBox FileName & " must be open!", vbCritical
Exit Sub
End If
Msg = "This macro will replace Module1 in record.XLSM "
Msg = Msg & "with an updated rvcform." & vbCrLf & vbCrLf
Msg = Msg & "Click OK to continue."
If MsgBox(Msg, vbInformation + vbOKCancel) = vbOK Then
Call ReplaceModule
Call ReplaceUserform1
Call ReplaceUserform2
Call ReplaceUserform3
Call ReplaceUserform4
Call ReplaceUserform5
Call ReplaceUserform6
Call ReplaceUserform7
Call ReplaceUserform8
Else
MsgBox "Module not replaced!", vbCritical
End If
End Sub
Sub ReplaceModule()
Dim FileName
' Export Module1 from this workbook
FileName = ThisWorkbook.Path & "\tempmodxxx.bas"
ThisWorkbook.VBProject.VBComponents("rvcform") _
.Export FileName
' Replace Module1 in UserBook
Set VBP = ActiveWorkbook.VBProject
' Dim VBProj As Object
'Set VBProj = WB.VBProject
' Application.ScreenUpdating = False
'Ne peut procéder si le projet est non-protégé.
' If VBP.Protection <> 1 Then Exit Sub
'Set Application.VBE.ActiveVBProject = VBP
'Utilisation de "SendKeys" Pour envoyer le mot de passe.
'SendKeys "123456"
'SendKeys "123456"
'Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute
'Application.Wait (Now + TimeValue("0:00:1"))
On Error GoTo ErrHandle
Call Workbook_open
With VBP.VBComponents
.Remove VBP.VBComponents("rvcform")
.Import FileName
End With
' Delete the temorary module file
Kill FileName
MsgBox "The rvcform has been replaced.", vbInformation
Exit Sub
ThisWorkbook.VBProject.Save
ErrHandle:
' Did an error occur?
MsgBox "ERROR. The rvcform may not have been replaced.", _
vbCritical
End Sub
Private Sub Workbook_open()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Protect Password:="xxxxxx", _
UserInterFaceOnly:=True
Next wSheet
End Sub
Sub ReplaceUserform1()
Dim FileName
' Export Module1 from this workbook
FileName = ThisWorkbook.Path & "\tempmodxxx.bas"
ThisWorkbook.VBProject.VBComponents("UserForm1") _
.Export FileName
' Replace Module1 in UserBook
Set VBP = ActiveWorkbook.VBProject
On Error GoTo ErrHandle
Dim vbProj As Object
Set vbProj = ThisWorkbook.VBProject
If vbProj.Protection <> 1 Then Exit Sub ' already unprotected
Set Application.VBE.ActiveVBProject = vbProj
SendKeys Pwd & "yyyyyyy"
Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute
With VBP.VBComponents
' .Remove VBP.VBComponents("UserForm1")
.Import FileName
End With
' Delete the temorary module file
' Kill FileName
'MsgBox "Received Date Calender has been replaced.", vbInformation
Exit Sub
ErrHandle:
' Did an error occur?
MsgBox "ERROR. Received Date Calender may not have been replaced.", _
vbCritical
End Sub
Sub ReplaceUserform2()
Dim FileName
' Export Module1 from this workbook
FileName = ThisWorkbook.Path & "\tempmodxxx.bas"
ThisWorkbook.VBProject.VBComponents("UserForm2") _
.Export FileName
' Replace Module1 in UserBook
Set VBP = ActiveWorkbook.VBProject
On Error GoTo ErrHandle
With VBP.VBComponents
.Remove VBP.VBComponents("UserForm2")
.Import FileName
End With
' Delete the temorary module file
Kill FileName
'MsgBox "Date2 Calender has been replaced.", vbInformation
Exit Sub
ErrHandle:
' Did an error occur?
MsgBox "ERROR. Date2 Calender may not have been replaced.", _
vbCritical
End Sub
Sub ReplaceUserform3()
Dim FileName
' Export Module1 from this workbook
FileName = ThisWorkbook.Path & "\tempmodxxx.bas"
ThisWorkbook.VBProject.VBComponents("UserForm3") _
.Export FileName
' Replace Module1 in UserBook
Set VBP = ActiveWorkbook.VBProject
On Error GoTo ErrHandle
With VBP.VBComponents
.Remove VBP.VBComponents("UserForm3")
.Import FileName
End With
' Delete the temorary module file
Kill FileName
'MsgBox " Date3 Calender has been replaced.", vbInformation
Exit Sub
ErrHandle:
' Did an error occur?
MsgBox "ERROR. Date3 Calender may not have been replaced.", _
vbCritical
End Sub
Sub ReplaceUserform4()
Dim FileName
' Export Module1 from this workbook
FileName = ThisWorkbook.Path & "\tempmodxxx.bas"
ThisWorkbook.VBProject.VBComponents("UserForm4") _
.Export FileName
' Replace Module1 in UserBook
Set VBP = ActiveWorkbook.VBProject
On Error GoTo ErrHandle
With VBP.VBComponents
.Remove VBP.VBComponents("UserForm4")
.Import FileName
End With
' Delete the temorary module file
Kill FileName
'MsgBox "Date4 Calender has been replaced.", vbInformation
Exit Sub
ErrHandle:
' Did an error occur?
MsgBox "ERROR. Date4 Calender may not have been replaced.", _
vbCritical
End Sub
Sub ReplaceUserform5()
Dim FileName
' Export Module1 from this workbook
FileName = ThisWorkbook.Path & "\tempmodxxx.bas"
ThisWorkbook.VBProject.VBComponents("UserForm5") _
.Export FileName
' Replace Module1 in UserBook
Set VBP = ActiveWorkbook.VBProject
On Error GoTo ErrHandle
With VBP.VBComponents
.Remove VBP.VBComponents("UserForm5")
.Import FileName
End With
' Delete the temorary module file
Kill FileName
' MsgBox "Date5 Calender has been replaced.", vbInformation
Exit Sub
ErrHandle:
' Did an error occur?
MsgBox "ERROR. Date5 Calender may not have been replaced.", _
vbCritical
End Sub
Sub ReplaceUserform6()
Dim FileName
' Export Module1 from this workbook
FileName = ThisWorkbook.Path & "\tempmodxxx.bas"
ThisWorkbook.VBProject.VBComponents("UserForm6") _
.Export FileName
' Replace Module1 in UserBook
Set VBP = ActiveWorkbook.VBProject
On Error GoTo ErrHandle
With VBP.VBComponents
.Remove VBP.VBComponents("UserForm6")
.Import FileName
End With
' Delete the temorary module file
Kill FileName
'MsgBox "Date6 Calender has been replaced.", vbInformation
Exit Sub
ErrHandle:
' Did an error occur?
MsgBox "ERROR. Date6 Calender may not have been replaced.", _
vbCritical
End Sub
Sub ReplaceUserform7()
Dim FileName
' Export Module1 from this workbook
FileName = ThisWorkbook.Path & "\tempmodxxx.bas"
ThisWorkbook.VBProject.VBComponents("UserForm7") _
.Export FileName
' Replace Module1 in UserBook
Set VBP = ActiveWorkbook.VBProject
On Error GoTo ErrHandle
With VBP.VBComponents
.Remove VBP.VBComponents("UserForm7")
.Import FileName
End With
' Delete the temorary module file
Kill FileName
' MsgBox "7 Date Calender has been replaced.", vbInformation
Exit Sub
ErrHandle:
' Did an error occur?
MsgBox "ERROR. Date7 Calender may not have been replaced.", _
vbCritical
End Sub
Sub ReplaceUserform8()
Dim FileName
' Export Module1 from this workbook
FileName = ThisWorkbook.Path & "\tempmodxxx.bas"
ThisWorkbook.VBProject.VBComponents("UserForm8") _
.Export FileName
' Replace Module1 in UserBook
Set VBP = ActiveWorkbook.VBProject
On Error GoTo ErrHandle
With VBP.VBComponents
.Remove VBP.VBComponents("UserForm8")
.Import FileName
End With
' Delete the temorary module file
Kill FileName
MsgBox "All Forms have been replaced.", vbInformation
Exit Sub
ErrHandle:
' Did an error occur?
MsgBox "ERROR. Forms may not have been replaced.", _
vbCritical
End Sub
Last edited:
