Need advise on whether or not what I want to do is even possible... Pop up box that auto-populates next available...

[MJ72]

Hello All,
I currently have a spreadsheet that acts like a call log (Sheet1)(Call Log). In one of the cells I have a drop down menu that allows for a "yes" or "no" answer. I'd like to create a code that when the answer is "yes" a pop box appears with another drop down menu with 3 choices (let's say A, B, or C) and then have the selected answer auto populate into the next available cell in a specific column of a table (cells M3:M12)on another sheet of the workbook (Sheet 5)(P&A). Is this possible and if so, how? Thanks.

 

[bobsan42]

Yes it is possible. Depending on your coices and their complexity it can be done in various ways, but maybe "the traditional way" is to create a UserForm.
However I would not necessarily prefer a dropdown combobox for this case - it's a bit clumsy for my taste. Maybe consider using option buttons or Listbox - if nothing else you'll reduce the number of mouse clicks.

 

[MJ72]

Yes it is possible. Depending on your coices and their complexity it can be done in various ways, but maybe "the traditional way" is to create a UserForm.
However I would not necessarily prefer a dropdown combobox for this case - it's a bit clumsy for my taste. Maybe consider using option buttons or Listbox - if nothing else you'll reduce the number of mouse clicks.

Fair enough, thanks for the input, Bob. So being the rookie that I am, my next question would be how to make that happen...I dont have the VBA knowledge to pull it off.

 

[Gokhan Aycan]

I got a question, what if a previously used cell's dropdown gets changed accidentally? Since the column on Sheet5 is first one gets served, as far as I get it, how will you handle that?

 

[MJ72]

I got a question, what if a previously used cell's dropdown gets changed accidentally? Since the column on Sheet5 is first one gets served, as far as I get it, how will you handle that?

Good question. ?

 

[Gokhan Aycan]

Possible to add a column on Sheet1 (can be hidden) or perhaps another hidden sheet that will hold info about which row on Sheet1 correspond to Sheet5 row? Design-wise I mean, of course it is possible otherwise

 

[MJ72]

Possible to add a column on Sheet1 (can be hidden) or perhaps another hidden sheet that will hold info about which row on Sheet1 correspond to Sheet5 row? Design-wise I mean, of course it is possible otherwise

Any help with the VBA?

 

[Gokhan Aycan]

The form and code is easy when you decide how would you like to handle the exceptions and errors, people do make mistakes.

Just an example userform (using option buttons as bobsan42 has suggested):

A wrong option can be selected and entered into Sheet5. A previous Yes/No can be altered by mistake, and we have to be able to read the previous selection in that case for example. Stuff like that, you have to look at your workbook, visualize everything you can think of, and just write down how you would like it to behave. Don't think about the coding part for now.

 

[MJ72]

K...Maybe it would be better if you guys could see my screen...pic one is the call log, pic two is what I'm trying to populate with the call log info.

When a "Yes" is entered into "Count As Info Session", I need it to auto populate the Entry Screen next available line. I can do it currently but it copies to the line of the entry in the call log. The other element I mentioned wanting was that when that "Yes" is selected on the Call Log another selection tool appears (whether it be a user form or drop down) to select one of the three answers available for the "Type de Seances" column in Entry Screen. Does this help?

 

[Gokhan Aycan]

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).

Public TargetRow As Long
Public SelectedOption As String

Paste the following in CallLog sheet module.

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.

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.