vba useform to patch formula in excel files

[ezonemy]

hi, im having multiple excel files in a folder which is password protected (same password for all files). I need a userform with 4 text box (textbox 1 = to input forlder name to be opened, textbox 2 = cell to find, e.g a1... textbox 3= to select sheet e.g Sheet 1 , textbox 4= to input formula e.g "=a1+a2+a3")

basically this userform will open all the excel files in the folder which was selected based on textbox 1, unprotect the sheet based on the input in text box 3 and find the cell based on textbox 2 and insert the formula based on textbox 4 then later protect the sheet back with same password and save the files. the mcaro need to loop to all excel files in the folder which selected based on textbox 1.

Hope some one can help on the vba code for this process.

Thank you.

 

[Worf]

Hi
Please test this:

Private Sub cbutGo_Click()
Dim wb As Workbook, pth$, ext$, f$


pth = Me.TextBox1.Value         ' example: "c:\pub\source\"
ChDir pth
ext = Dir("*.xls?")
Do While ext <> ""
    Set wb = Workbooks.Open(pth & ext, , , , "password")
    wb.Worksheets(Me.TextBox3.Value).Range(Me.TextBox2).Formula = Me.TextBox4.Value
    wb.Protect "password", True, True
    wb.Save
'   wb.Close
    ext = Dir
Loop
End Sub

 

[ezonemy]

hi its working, just that the workbook is not protected, its the worksheet which is protected with same password for all sheets. Also noted the vba code above makes the workbook to hang and unable to close.

 

[Worf]

New version:

' at a standard module
Sub showuf2()


UserForm2.Show vbModeless


End Sub

' UserForm module
Private Sub cbutGo_Click()
Dim wb As Workbook, pth$, ext$, f$, ws As Worksheet
pth = Me.TextBox1.Value         ' example: "c:\pub\source\"
ChDir pth
ext = Dir("*.xls?")
Do While ext <> ""
    Set wb = Workbooks.Open(pth & ext)
    Set ws = wb.Worksheets(Me.TextBox3.Value)
    If ws.ProtectContents Then ws.Unprotect "password"
    ws.Range(Me.TextBox2).Formula = Me.TextBox4.Value
    ws.Protect "password"
    wb.Save
Rem wb.Close        ' to close the workbook, delete the Rem keyword
    ext = Dir
Loop
End Sub