Excel VBA combo box 1

[andycogress]

I've currently got the following bit of code in a macro:

user = Application.InputBox("Enter a user")

This pops up a box that asks for a user name which it then goes off and does something with.

However, I want to show a combo box with just the users available. The users are in a sheet named "User Input" in column A - is there a way of populating the combo box from there with the names? Also, the usernames may appear more than once so it should only do unique names. Am I pushing the possibilities of VBA?

(you'll probably guess, I'm not a great VBA programmer!)

 

[MajP]

1)In vba build yourself a user form called:
frmMyInput
2) Put a combo on the form and call it something like
cmboNames
3) In the form put code like this where the rowsource is a range of names

Private Sub UserForm_Initialize()
  Me.cmboNames.RowSource = "a1:a3"
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  glblUser = cmboNames.Value
End Sub

Build a standard module and put some code like:

Public Sub yourCode()
  Dim user As String
  'your code
  user = myInputBox
   MsgBox user
  'more your code using the user variable
End Sub

Public Function myInputBox()
  frmMyInput.Show
  'Since the form is modal code execution stops until
  'you finally close the form
  myInputBox = glblUser
End Function

 

[andycogress]

Brilliant, thanks!

 

[CautionMP]

andycogress,
You can create your own input box by using a custom UserForm in conjunction with a standard module.

You will need to create a new UserForm with the details listed below.
UserFrom1
[tab]Name : UserForm1
[tab]Control : ComboBox
[tab][tab]Name : cmbUser
[tab]Control : CommandButton
[tab][tab]Name : cmdClose

Now add the following code to the form and save it.

Private Sub cmdClose_Click()
  Me.Hide
End Sub

Private Sub UserForm_Initialize()
  Call cmbUserLoad
End Sub

Sub cmbUserLoad()
  Dim colUser As New Collection
  Dim rngUser As Range
  Dim strBuffer As String
  
  On Error Resume Next
  For Each rngUser In ThisWorkbook.Worksheets("User Input").UsedRange.Rows
    strBuffer = rngUser.Columns("A").Value
      If Trim$(strBuffer) <> "" Then
      colUser.Add strBuffer, strBuffer
      If Err.Number = 0 Then
        Me.cmbUser.AddItem strBuffer
      Else
        Err.Clear
      End If
    End If
  Next rngUser
  On Error GoTo 0
  
  Set rngUser = Nothing
  Set colUser = Nothing
End Sub[code]

 Create a new blank module and add the following code.

[code=Module1]Public Function InputBoxCustom() As String
  Load UserForm1
  UserForm1.Show
  InputBoxCustom = UserForm1.cmbUser.Value
  Unload UserForm1
End Function

Now you should be able to replace [tt]Application.InputBox("Enter a user")[/tt] with [tt]InputBoxCustom[/tt] in your code.

Hope this helps,
CMP


[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)