Input box query

[Chewyhairball]

Hi Guys

column A on my spreadsheet is used for inputting the date and column c is used for inputting Bin1 or Bin2 or Bin 3 on the same row.
I would like if possible for an input box to appear when a date is entered in column a and for the input box to offer 3 choices ( bin 1, bin 2, bin 3). You would select which one you want and it inputs your choice into column c.

I am not looking for a data validation list as I can do that but an input box with multiple choices.

thank you in advance

rory

 

[Yongle]

Try something like this in a COPY of your workbook

Assumes ListBox is active-X control ListBox1

Place code in SHEET module

Option Explicit
Private Cel As Range

Private Sub ListBox1_Change()
    Dim X As Long
    With ListBox1
        For X = 0 To .ListCount - 1
            If .Selected(X) And Not Cel Is Nothing Then Cel.Value = .List(X)
        Next X
    End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim T As Range: Set T = Target
    If T.Row > 1 And T.Column = 1 Then
       T.Select
       Set Cel = T.Offset(, 2)
        With ListBox1
            .Left = Target.Offset(, 1).Left
            .Top = Target.Top
            .Activate
        End With
    End If
End Sub

One way to populate the listbox

Private Sub PopulateListbox()
    Dim i As Variant
    With ListBox1
        .Clear
        For Each i In Array("bin 1", "bin 2", "bin 3")
            .AddItem i
        Next
    End With
End Sub

 

[MickG]

Possible option:-
NB:- Paste code in Worksheet Module

Code will run after input in column "A", then select from Msgbox that shows.

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Ans [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]If[/COLOR] Target.Count = 1 And Not Intersect(Range("A:A"), Target) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
Ans = MsgBox("Yes = Bin 1." & Space(10) & "No = Bin 2." & Space(10) & "Cancel = Bin 3.", vbYesNoCancel)

[COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] Ans
[COLOR="Navy"]Case[/COLOR] 6: Target.Offset(, 2) = "Bin 1."
[COLOR="Navy"]Case[/COLOR] 7: Target.Offset(, 2) = "Bin 2."
[COLOR="Navy"]Case[/COLOR] 2: Target.Offset(, 2) = "Bin 3."
[COLOR="Navy"]End[/COLOR] Select

[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Yongle]

@MickG
Simple is best!
https://www.mrexcel.com/forum/members/mickg.html

 

[MickG]

Thanks for the comment, its a pity it doesn't seem possible to change the text on the Buttons !!!

 

[Yongle]

@MickG
Agreed but a simple userform would do the job

@Chewyhairball
- I am guessing that there are more than three bins
- if so, you could create a simple userform to mimic @mickG solution

 

[Chewyhairball]

Hi Mick

i pasted that into my sheet but it does not seem to do anything when I put a date in column a. I also tried it in the workbook module but no luck ��

 

[Yongle]

It works for me
Did you put it in the sheet module?
right click on sheet tab \ view code \ paste in code window

 

[Chewyhairball]

Hi yongle

im not really sure what to do with active x list box ����

thanks Rory

 

[Yongle]

Add an active-x listbox
developer tab \ Insert \ active-x listbox

Assuming it is ListBox1 the code does the rest