Hi all,
I have written code that does exactly what I want it to do; which is find information from another workbook and populate a form so that a user can edit it. The problem is that my code opens the workbook and then closes it again which means that it takes too long. This function will be used hundreds of times a day by a number of users and taking ten seconds each time you open a record is not an option.
I'm pretty sure that I can do what I want to without opening the other workbook which will save a huge amount of time. Any advice would be greatly appreciated on how to change my code to speed it up: (The bold, italic section is probably the most relevant but I have posted the whole code.)
Dim LeadIDInt As Long
Private Sub UserForm_Initialize()
Application.ScreenUpdating = False
If LeadID = "" Then
MsgBox ("Please select a Lead to Edit") ' There is a listbox before this form to select the LeadID
Unload Me
End If
'Initialise Ranges to store Salesmen and Agents in
Dim cSalesman As Range
Dim cAgentName As Range
Dim cBusinessArea As Range
Dim cLeadSource As Range
LeadIDInt = CLng(LeadID)
'Open Workbook to get values
Dim Workbook As String
Workbook = "R:\hLog\" & "hLog" & ".xlsm"
On Error Resume Next
Workbooks.Open (Workbook), UpdateLinks:=xlUpdateLinksAlways
Worksheets("Lead Log").Select
'Name Worksheets for ease of reference
Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets("Info - Agents")
Dim wd As Worksheet
Set wd = ActiveWorkbook.Worksheets("SalesmanList")
'Fill Salesman ComboBox with all Salesmen
For Each cLeadSource In wd.Range("LeadSourceList")
With Me.ComboLeadSource
.AddItem cLeadSource.Value
End With
Next cLeadSource
'Fill Salesman ComboBox with all Salesmen
For Each cSalesman In wd.Range("SalesmanList")
With Me.ListSalesman
.AddItem cSalesman.Value
End With
Next cSalesman
'Add all telesales members to that list as well
For Each cSalesman In wd.Range("TelesalesList")
With Me.ListSalesman
.AddItem cSalesman.Value
End With
Next cSalesman
'Fill Agents ComboBox with all Agents
For Each cBusinessArea In wd.Range("BusinessAreaList")
With Me.ComboBusinessArea
.AddItem cBusinessArea.Value
End With
Next cBusinessArea
'Fill Business Area ComboBox with all Business Areas
For Each cAgentName In ws.Range("AgentList")
With Me.ListAgentName
.AddItem cAgentName.Value
End With
Next cAgentName
'Find selected row
Dim Row As Integer
Row = Application.WorksheetFunction.Match(LeadIDInt, Sheets("Lead Log").Range("LeadIDList"), 0)
'Fill fields with correct data
If Cells(Row + 4, 1) <> "" Then
TextDate.Value = Cells(Row + 4, 1)
End If
If Cells(Row + 4, 3) <> "" Then
TextClientName.Value = Cells(Row + 4, 3)
End If
ListSalesman.Value = Cells(Row + 4, 2)
ComboLeadSource.Value = Cells(Row + 4, 5)
ListAgentName.Value = Cells(Row + 4, 6)
If Cells(Row + 4, 4) <> "" Then
TextPostCode.Value = Cells(Row + 4, 4)
End If
If Cells(Row + 4, 9) <> "" Then
ComboBusinessArea.Value = Cells(Row + 4, 9)
End If
'Close Lead Log to prevent unauthorised access and clogging up users
ActiveWorkbook.Close True
End Sub
Bookmarks