Hi all,
I have created the below userform in order for my manager to be able to make changes to an Agent's results by quarter...
The textboxes under the heading "Current Record" are poplulated when both Agent AND Quarter have been selected from the comboboxes above and the "Retrieve" button has been clicked. I have used the following code to achieve this (based on other code people have helped me with on here)
What I want to achieve is when my manager inputs new results in the textboxes under the heading "Amendment", and clicks the "CHANGE" button, the new results are input onto the sheet entitled "QtrData" in the correct columns against the correct agent (as shown in the code above).
There will be instances where my manager may not want to change the results for all of the entries, so where the corresponding textbox is left blank I want the result to stay the same as it is.
Can anyone help me with this?
I have created the below userform in order for my manager to be able to make changes to an Agent's results by quarter...
The textboxes under the heading "Current Record" are poplulated when both Agent AND Quarter have been selected from the comboboxes above and the "Retrieve" button has been clicked. I have used the following code to achieve this (based on other code people have helped me with on here)
Code:
Private Sub CommandButton5_Click()
Dim criteria1 As String
Dim criteria2 As String
Dim rng As Range
Dim result
criteria1 = ComboBox1
criteria2 = ComboBox2
If Trim(criteria1) <> "" And Trim(criteria2) <> "" Then
result = Application.Match(criteria1, Worksheets("QtrData").Range("B:B"), 0)
If Not IsError(result) Then
With Worksheets("QtrData")
If criteria2 = "1" Then
TextBox30.Text = .Range("G:G")(result).Text
TextBox28.Text = .Range("K:K")(result).Text
TextBox26.Text = .Range("P:P")(result).Text
TextBox24.Text = .Range("T:T")(result).Text
TextBox18.Text = .Range("Z:Z")(result).Text
Else
If criteria2 = "2" Then
TextBox30.Text = .Range("AF:AF")(result).Text
TextBox28.Text = .Range("AJ:AJ")(result).Text
TextBox26.Text = .Range("AO:AO")(result).Text
TextBox24.Text = .Range("AS:AS")(result).Text
TextBox18.Text = .Range("AY:AY")(result).Text
Else
If criteria2 = "3" Then
TextBox30.Text = .Range("BE:BE")(result).Text
TextBox28.Text = .Range("BI:BI")(result).Text
TextBox26.Text = .Range("BN:BN")(result).Text
TextBox24.Text = .Range("BR:BR")(result).Text
TextBox18.Text = .Range("BX:BX")(result).Text
Else
If criteria2 = "4" Then
TextBox30.Text = .Range("CD:CD")(result).Text
TextBox28.Text = .Range("CH:CH")(result).Text
TextBox26.Text = .Range("CM:CM")(result).Text
TextBox24.Text = .Range("CQ:CQ")(result).Text
TextBox18.Text = .Range("CW:CW")(result).Text
End If
End If
End If
End If
End With
End If
Else
MsgBox "Please select Agent Name AND Quarter"
End If
End SubWhat I want to achieve is when my manager inputs new results in the textboxes under the heading "Amendment", and clicks the "CHANGE" button, the new results are input onto the sheet entitled "QtrData" in the correct columns against the correct agent (as shown in the code above).
There will be instances where my manager may not want to change the results for all of the entries, so where the corresponding textbox is left blank I want the result to stay the same as it is.
Can anyone help me with this?
