Userform to edit macro?

**mungel** · 11-25-2011, 10:42 PM

Hi,

I had a little search but couldn't find anything on the subject.
I'm wondering if its possible to create a userform that can update code in a macro?

Specifically, I have a macro that updates external data but the link for this external data changes weekly. So rather than have to edit the macro manually, the userform will do it when requested.

Here is an example of the code, with the sections required to be changed highlighted in red:

ActiveWorkbook.UpdateLink Name:="T:\Filepath\Data\week 36\file wk36.xlsx", Type:= _
xlExcelLinks

Thanks in advance for any time or assistance with this.

**pike** · 11-26-2011, 01:44 AM

hi mungel
with your userform

ActiveWorkbook.UpdateLink Name:="T:\Filepath\Data\week " & Texbox1.value & "\file wk" & Texbox1.value & ".xlsx", Type:= _
xlExcelLinks

where textbox1 has the 36 text

**royUK** · 11-26-2011, 03:52 AM

You could add a Browse for file button or simply ask the user within the code to select a file

Option Explicit

Sub openfile()
    Dim sFil As String
    Dim sTitle As String
    Dim sWb As String
    Dim iFilterIndex As Integer

    On Error GoTo err_handler
    ' Set up list of file filters
    sFil = "Excel Files (*.xls),*.xls"
    ' Display *.xls by default
    iFilterIndex = 1
    ' Set the dialog box caption
    sTitle = "Select  File to Open"
    ' Get the filename
    sWb = Application.GetOpenFilename(sFil, iFilterIndex, sTitle)

    Workbooks.Open Filename:=sWb
    'rest of code here
    Exit Sub
err_handler:
    MsgBox "No selection made", vbCritical, "Cancelled"
End Sub

**mungel** · 11-26-2011, 05:42 AM

Hi,

Thanks for the replies.
Both are excellent ideas and I will try them both out to see which fits better.
The general idea is to make the weekly update process easy enough for anyone to do.
Unfortunately, that means 'really' computer illiterate people in some cases.

Many thanks.