No?
Basically, you said the same thing earlier. Tbh it does help to visualize better, but you haven't told anything about the concerns I mentioned. So I will just try to help with your question. You can always ask for improvements anyway.
If these two sheets will not be deleted then assign codenames to them in VBA Editor properties pane. Select the sheet in Project pane and enter "CallLog" and "PaA" for the (Name) property. You can change the names of course, but if you do also change the references in other places below.
Paste the following at the top before any subs in any standard module (insert one if there are none).
VBA Code:
Public TargetRow As Long
Public SelectedOption As String
Paste the following in CallLog sheet module.
VBA Code:
Dim bYesAlready As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, CallLog.Columns("N:N")) Is Nothing) Then
If Target.Cells.Count > 2 Or Target.Row = 1 Then Exit Sub
If UCase(Target.Value) = "YES" Then
If bYesAlready Then Exit Sub
TargetRow = Target.Row
frmOptionSelection.Show
End If
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not (Intersect(Target, CallLog.Columns("N:N")) Is Nothing) Then
If Target.Cells.Count > 1 Then Exit Sub
If UCase(Target.Value) = "YES" Then
bYesAlready = True
Else
bYesAlready = False
End If
End If
End Sub
Insert a new userform, name it "frmOptionSelection" and add 3 Option Buttons, I kept the names default. Add a command button and name it "cmdSelect".
Paste the following to the userform's module.
VBA Code:
Private Sub cmdSelect_Click()
If SelectedOption <> "" Then
Dim rng As Range
Set rng = PaA.Range("L1").CurrentRegion
Dim NextRow As Long
NextRow = rng.Rows.Count + 1
PaA.Range("L" & NextRow).Value = CallLog.Range("H" & TargetRow).Value ' Date
PaA.Range("M" & NextRow).Value = SelectedOption ' SelectedOption
PaA.Range("N" & NextRow).Value = CallLog.Range("A" & TargetRow).Value ' Company
PaA.Range("O" & NextRow).Value = CallLog.Range("C" & TargetRow).Value ' Contact
'PaA.Range("P" & NextRow).Value = CallLog.Range("H" & TargetRow).Value ' Interaction
Set rng = Nothing
SelectedOption = ""
Me.Hide
End If
End Sub
Private Sub OptionButton1_Click()
SelectedOption = "A"
End Sub
Private Sub OptionButton2_Click()
SelectedOption = "B"
End Sub
Private Sub OptionButton3_Click()
SelectedOption = "C"
End Sub
Private Sub UserForm_Activate()
Me.Top = Application.Top + Application.Height / 2 - Me.Height / 2
Me.Left = Application.Left + Application.Width / 2 - Me.Width / 2
Me.OptionButton1.Value = False
Me.OptionButton2.Value = False
Me.OptionButton3.Value = False
SelectedOption = ""
cmdSelect.SetFocus
End Sub
Private Sub UserForm_Initialize()
Me.StartUpPosition = 0
End Sub
Change the SelectedOption variables to the suitable choices under OptionButton#_Click events.
Also, I was not sure which column the option was going to be pasted and assumed M. Change it and the Interaction columns as necessary.
The option form will not show if the Yes/No selection cell already has a "yes" on it when you select it. However, as is, if a wrong selection is made there is no correction other than going into PaA sheet and manually change the option.
