Using UF information in a macro

[RusheR]

I have a Macro that brings up a userform.

'Run UserForm
    UserForm_SelectAccount.Show

In that UF I have a combobox that lists bank accounts. I want the bank account that's chosen to be then thrown over into my macro where it uses the data.

I can't figure out how to do this (past a very primative way of copying the data to the sheet in the UF then when the macro finishes it gets the data and clears it.

Can anyone please help?

I though this would work but it doesn't...

MsgBox (UserForm_SelectAccount.ComboBox_Accounts.Text)

 

[MisterCrash]

Is the macro contained within the code for the UserForm itself, or is it in an external module?

 

[RusheR]

It's an external module.

I run the macro than it brings up the UF. Once the user form is finished it goes back to the macro and picks up where it left off.

THis is the only way I know how to do it.

 

[YasserKhalil]

Try this slight change

MsgBox (UserForm_SelectAccount.ComboBox_Accounts.Value)

 

[MisterCrash]

You could try declaring a variable in the main macro, then assigning the ComboBox text to that variable while the UserForm is still up. That way, when the form is closed, the text should be retained in memory.

 

[RusheR]

That doesn't seem to work.

This is my macro...

Dim AccountName As String
'Run UserForm
UserForm_SelectAccount.Show

MsgBox (AccountName)

and this is my userform...

Private Sub CommandButton_Okay_Click()

AccountName = ComboBox_Accounts.Value
Unload Me
Exit Sub
End Sub

Any idea why it's not working???

 

[al_b_cnu]

Hi,

You'll need to do something like this:

In Userform:

Option Explicit

Private Sub btnExit_Click()
Me.Hide
End Sub

Private Sub ComboBox_Accounts_Change()
btnExit.Enabled = ComboBox_Accounts.ListIndex >= 0
End Sub

Private Sub UserForm_Initialize()
With ComboBox_Accounts
    .AddItem "A"
    .AddItem "B"
    .AddItem "C"
End With
btnExit.Enabled = False
End Sub
'*** disable close via little 'x' ***
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If Cancel <> 1 Then
    Cancel = -1
    End If
End Sub

The calling code:

Dim frmSelAccounts As Userform_SelectAccounts

Set frmSelAccounts = New Userform_SelectAccounts
frmSelAccounts.Show
MsgBox frmSelAccounts.ComboBox_Accounts.Value
Set frmSelAccounts = Nothing