Use same UserForm

[mqdias]

Hi,

I want to make something that i didn't found yet how to do it.
I have several CommandButtons, to "Add" items, when i click in any one of them, it appears an UserForm with a textbox and a commandbutton, so that i can write what i want and click "OK" to add it.
The problem is that i have to have one little Userform like this to each one of the commandbuttons.

Is there any way that i can use always the same UserForm and recognize from wich commandbutton was made the instruction, to make this "Add" operation?

Thanks

[Dr.K]

Sure, but you need to be a little more clear about what you want...

It depends on where those command buttons are and where the code is running from.

I think the simplest scenario is if you have a userform with commandbuttons, and then another userform to pop-up for the Notes/Memos. In the button_Click() code, set up the second UserForm as you need: set the Caption text, set or clear out the TextBox text as required, and then Show it. When done, hide it, but don't unload it. Making a variable for a specific instance of the UserForm will help.

When dealing with DataBase Records, I often use this one UserForm over and over ago for Memo fields. Its just a textBox and an "OK" command button. I dim it as Public in the header of my main code, and then ANY sub or UserForm in my project can use it. Just make sure you Unload it when you are all done.

Here is the ufMemoBox code:
(all the confusing API stuff is just there to turn off the standard Windows "window close button")

[vba]
'User Form: Displays DB Memo fields. Also allows user to edit and compose.

Option Explicit 'Delete this line if you get "Undeclared Variable" errors

'declare Windows API Function calls (used to disable userform X box)
Private Declare Function findwindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetSystemMenu Lib "user32" _
(ByVal hWnd As Long, ByVal bRevert As Long) As Long
Private Declare Function DeleteMenu Lib "user32" _
(ByVal hMenu As Long, ByVal nPosition As Long, ByVal wFlags As Long) As Long
'declare constants and variables used for Windows API calls
Private Const SC_CLOSE As Long = &HF060
Private hWindForm As Long
Private hMenu As Long
'

Private Sub UserForm_Initialize()
'this part of the sub uses windows API calls to disable the X button
hWindForm = findwindow("thunderDframe", Me.Caption)
hMenu = GetSystemMenu(hWindForm, 0)
DeleteMenu hMenu, SC_CLOSE, 0&
End Sub

Private Sub cbutOK_Click()

Me.Hide
End Sub[/vba]


Here is me declaring the public variable:
[vba]Public ufoMemoBox As ufMemoBox[/vba]

This goes in your main code, or in the Initialize() routine for the main user form:
[vba]Set ufoMemoBox = New ufMemoBox[/vba]

And here is what calling it looks like:
(here I have columns from a Recordset stored in a ListBox, with the memo in an invisible column)

[vba]Private Sub cbutViewNotes_Click()

Dim strMemo As String
If Me.listLineUp.ListIndex = -1 Then Exit Sub

strMemo = Me.listLineUp.Column(3)

If strMemo = Empty Then
MsgBox "There are no notes saved for this Record.", vbExclamation _
, "No Notes to display!"
Else
ufoMemo.tbMemo.Text = strMemo
ufoMemo.Caption = " Notes for LineUp dated: " _
& Me.listLineUp.Column(0) & " Notes by: " & Me.listLineUp.Column(2)
ufoMemo.Show
End If
End Sub[/vba]

[Bob Phillips]

In the userform

[VBA]

Public Caller As String

Private Sub UserForm_Activate()
MsgBox Caller
End Sub
[/vba]

and in a code module

[VBA]

Public Sub ShowMyForm()
Dim myForm As UserForm1
Set myForm = New UserForm1
With myForm .Caller = Application.Caller
.Show
End With
End Sub
[/vba]

[mqdias]

Thank you so much both!!!

Dr. K, thank you for explaining this so carefully!!!
It's great! I'll try it!!

xld, Thank you also. I know that you mentioned little alternatives to Dr.K post, but can you specify wher must they be put?


Thank you once more!!

[Bob Phillips]

It's in my post.