Return Value from UserForm

[Blitskrieg]

I'm trying to put something together that would allow me to call up a UserForm based on a cells value and once it is displayed select a value from the combo box in it and return that value to a specific cell.

Eg: If A1= "Blue", UserForm with Combo Box is displayed, Value Selected from the Combo Box is returned to the worksheet at C1.

Is this possible?

[Zack Barresse]

Hi there, welcome to the board!

I would do this with three code modules. The worksheet module, a custom userform (my control names will probably not be the same as yours), and a standard module. The names will need to match in order to work correctly.

In the WORKSHEET module:
[vba]Option Explicit

Const sTarget As String = "A1"
Const sDestAddy As String = "C1"

Private Sub Worksheet_Change(ByVal Target As Range)
Dim uf As New frmMyUF
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range(sTarget)) Is Nothing Then Exit Sub
If UCase(Target.Value) = "BLUE" Then
Load uf
uf.Label2.Caption = Me.Name & sDelim & sDestAddy
uf.Show
End If
End Sub[/vba]

In the USERFORM module:
[vba]Option Explicit

Private Sub CommandButton1_Click()
Dim ws As Worksheet, rDest As Range
If Len(Me.ComboBox1.Value) = 0 Then
MsgBox "You must choose a value first!", vbExclamation, "ERROR"
Exit Sub
End If
Set ws = ThisWorkbook.Worksheets(Split(Me.Label2.Caption, sDelim)(0))
Set rDest = ws.Range(Split(Me.Label2.Caption, sDelim)(1))
Application.EnableEvents = False
rDest.Value = Me.ComboBox1.Value
Application.EnableEvents = True
Unload Me
End Sub

Private Sub UserForm_Initialize()
Me.ComboBox1.AddItem "a"
Me.ComboBox1.AddItem "b"
Me.ComboBox1.AddItem "c"
Me.ComboBox1.ListIndex = 0
End Sub[/vba]

In the STANDARD MODULE:
[vba]Option Explicit

Public Const sDelim As String = "|"[/vba]

Basically there are 4 controls on the userform:
Label1: Label for the combobox
Label2: A hidden label, used to pass data from the worksheet
ComboBox1: The combobox used for the user to choose whatever with
CommandButton1: The command button on the userform used to enter data into the destination cell

You'll also have to change the destination cell and start cell in the worksheet module for that which is desired. Also, change the userform name (I named mine frmMyUF).

HTH

[mikerickson]

You could put this in the code module for userform1
[VBA]Function ValueSelected(colorEntered As String)
Me.Caption = colorEntered
Me.Show
ValueSelected = UserForm1.Tag
End Function

Private Sub butCancel_Click()
Unload Me
End Sub

Private Sub butOK_Click()
Me.Hide
End Sub

Private Sub ComboBox1_Click()
Me.Tag = Me.ComboBox1.Value
End Sub

Private Sub UserForm_Initialize()
With Me.ComboBox1
.AddItem "a"
.AddItem "b"
.AddItem "c"
.AddItem "d"
End With
End Sub[/VBA]
and this in the sheet's code module[VBA]Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$1" Then
Select Case LCase(CStr(Target.Value))
Case "blue"
Range("C1").Value = UserForm1.ValueSelected(Target.Value)
End Select
End If
End Sub[/VBA]Note the argument passed to ValueSelected and the Select Case in the Change event for handling other than "Blue" entry.

[Blitskrieg]

I certainly appreciate the code you gents have laid down. Seeing that it is possible I would like to give you the specifics now. I have attached a worksheet that shows exactly what I'm looking for and roughed up a UserForm to be called.

Ultimately what I would want to happen is:
A) A number is entered in Column A (Meternum)
B) If the value of A is in Column I (Multi Vessel Sites), display the UserForm
C) Return the value selected from the UserForm to Column C on the same row the value for Column A was entered.

So in short, everytime a value is entered in column a, query the number to see if it's a multi vessel location, if so display the userform, and return it's value two cells over.

[mikerickson]

In none of this has it been explained what the values in the combo box are to be. Both Zack and I used dummy entries for the combobox. In step B-C what values is the user to choose from?

[Blitskrieg]

Sorry. The combobox values are in the attached sheet and the userform. But they are as follows: Tank 1, Tank 2, Tank 3, Tank 4, Pit 1, Pit 2, Pit 3, Pit 4.

[mikerickson]

The attached has this in the userform's code module
[VBA]Function ValueSelected() As String
With Me
.Caption = "Select Tank Type"
.Show
End With
ValueSelected = UserForm1.ComboBox1.Value
Unload UserForm1
End Function

Private Sub CommandButton1_Click()
Rem OK button
UserForm1.Hide
End Sub

Private Sub CommandButton2_Click()
Rem cancel
UserForm1.Hide
End Sub

Private Sub UserForm_Initialize()
Dim oneCell As Range
With Me.ComboBox1
For Each oneCell In ThisWorkbook.Sheets("Sheet1").Range("F2:F9")
.AddItem CStr(oneCell.Value)
Next oneCell
End With
End Sub
[/VBA]
and this in Sheet 1's code module
[VBA]Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo Halt
Application.EnableEvents = False
With Target
If .Cells.Count = 1 And .Column = 1 Then
If IsNumeric(Application.Match(.Value, .Parent.Range("i:i"), 0)) Then
.Offset(0, 2).Value = UserForm1.ValueSelected
End If
End If
End With
Halt:
Application.EnableEvents = True
End Sub
[/VBA]

[Zack Barresse]

For future posts, you need to post ALL of your data and information, and if you need to use "dummy" data just make sure you say so. I don't have time to re-do multiple solutions, so I won't post anymore here, pragmatically no time. I'm posting this because it's general netiquette for you to do so, hope you understand.

[Blitskrieg]

Apologies again Zack.

I'm still having trouble executing this. I tried calling the form with a custom Function to no avail. I noticed that simple formulas are not working and I think there might be something wrong with my Excel 2007. I converted the file from Compatibility Mode and saved it. Any suggestions?

[Aussiebear]

Post your workbook, so we can see what you have done to date.

[Blitskrieg]

Here is my workbook. All I have added was the below code to see if I could call the UserForm from Excel with a macro which resulted in access denied.
I am using Excel 2007 .



[VBA]Sub MyForm()
UserForm1.Show
End Sub
[/VBA]

[Blitskrieg]

/bump

[Aussiebear]

Have a look through your code and see if there are any conflicts.

Make sure you have named everything correctly

[VBA]Private Sub UserForm_Initialize()
Dim oneCell As Range
With Me.ComboBox1
For Each oneCell In ThisWorkbook.Sheets("Sheet1").Range("F2:F9")
.AddItem CStr(oneCell.Value)
Next oneCell
End With
End Sub[/VBA]

[Tinbendr]

You have the Rowsource range listed in the ComboBox1 Properties. Having done that, you can't add to it using AddItem.

Use one or the other.

[mikerickson]

The posted workbook has a Worksheet_Change event that calls the userform and puts the selected value into the proper cell.

In the attached, I moved your MyForm sub into a normal module, rather than ThisWorkbook, but it will not put the chosen value into any cell.

In that same module, is a new sub, MakeCellEntry, which allows the user to select an entry from the userform and then designate a cell into which the selected value is put.