Re-use a Userfrom

[LGMan]

Hi, is it possible to re-use the same UserForm muliple times. I have a simple UserForm containing the Calender.
At the moment, the UserForm is hardcoded to cell H5.
I have 8 date fields, so can I re-use the UserForm? if so how?, or do I need 8 UserForms?
Here is the code for the UserForm

Private Sub Calendar1_Click()
Range("H5").Value = Calendar1.Value
UseForm1.Hide
Unload.UserForm1
End Sub

 

[Andrzejek]

Don't do 8 user forms that are the same.
Pass the cell address to your Form and use this address to place your date into that cell.

How and where do you 'start' your UserForm now?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[MajP]

Suppose you have a form called "Form1"
This will open two seperate instances that can be controlled.

  Public Sub TestMultiple()
  Dim frm1 As New Form1
  Dim frm2 As New Form1
  frm1.Show
  frm1.BackColor = vbRed
  frm2.Show
  frm2.BackColor = vbBlue
End Sub

to demo this make sure it does not open modal or you will get one opening then the other.

 

[Andrzejek]

I have this very simple example.

UserForm1 with one TextBox1

Option Explicit
Dim strAddress As String

Public Property Let MyCell(ByRef MyAddress As String)
strAddress = MyAddress
End Property

Private Sub UserForm_Terminate()
Range(strAddress).Value = TextBox1.Text
End Sub

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
    With UserForm1
        .MyCell = Target.Address
        .Show vbModal
    End With
    Application.EnableEvents = True
End Sub

If you click any cell, UserForm appears. Whatever you type in a text box and click on X, you get this text in whatever cell you cliked on. [tt]vbModal[/tt] will NOT allow you to do anything else unless you X in UserForm

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[MajP]

Sorry, did not actually read your post beyond if you can open up multiple instance of a form. Not really what you are asking. What you are really asking is how to return a value from a pop up form, without hard coding it in the userform.

Here is a simple trick to return a value from a pop up form. On the form you can have an ok and cancel button

Private Sub cmdCancel_Click()
 Unload Me
End Sub
Private Sub cmdOk_Click()
  Me.Hide
End Sub

so here is a pop up calendar, I can call to open the calendar and if the user hit OK it will return the date.
So this function can be called from anywhere.

Public Function getCalendarDate(Optional DefaultDate As Variant = Null) As Variant
  Dim fcCalendar As New FormCalendar
  Dim frm As Object
  getCalendarDate = DefaultDate
  If Trim(DefaultDate & " ") = "" Then
    DefaultDate = Date
  End If
  fcCalendar.InitializeCalendar frmCalendar, CDate(DefaultDate)
  frmCalendar.Show
  For Each frm In UserForms
    If frm.Name = "frmCalendar" Then
     getCalendarDate = fcCalendar.CalendarDate
    End If
  Next frm
End Function

so if the user hits ok the form is loaded but hidden. So you can reference a control on the hidden form. If they hit cancel it is unloaded

example of use

Public Sub Test()
  MsgBox "Date" & getCalendarDate("")
End Sub

 

[combo]

I assigned userform to right-click of the hot area. The code:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Me.Range("B2:D4")) Is Nothing Then
    Cancel = True
    With UserForm1
        Set .rngCalendar = Intersect(Target, Me.Range("B2:D4"))
        .Show
    End With
End If
End Sub

Public rngCalendar As Range

Private Sub cmdOK_Click()
rngCalendar = Date
End Sub

It is also possible to use RefEdit control to pick a range and set its value.

Another option is to use modeless userform and set date to ActiveCell, you can select another cell without closing the userform (don't use refedit control in this case, it freezes excel).



combo

 

[LGMan]

Thanks for all the replies, this thread may go sideways, but as I've already got a worksheet event, I've never had much success with multiple worksheet events. I have a Data Validation with a second Data Valudation that provides it's drop down list depending on what you choose from the first Data Validation. I use the Application.Sendkeys ("(%Down)") so that the 2nd Data Validation pulls down to reveal the choices, rather than needing to click on it. My end user loves this so I would be vexed to have to remove it

Thanks

 

[combo]

The problem with event procedures is that they can interfere or be called in series after single user's (or code) action. It may be necessary to temporarily disable events (as in Andy's example) or track and disable second event procedure execution with external variable. The good thing in the latter is that different event procedures have fixed order.

combo

 

[SkipVought]

but as I've already got a worksheet event...

Hmmmmm. I run sheets with multiple events, mostly Selection events, where in the table data in column X procedure A fires and in column Y procedure B fires, etc.

Takes a well thought out design and clear instructions where other users will be using.

BTW, you don't need a UserForm. You could place an ActiveX Calendar Control (12.0 in my 2007) where on a single cell selection in the data for column X in your table, the control becomes VISIBLE and positioned relative to the selected cell. After a selection in the control the value is assigned to the selected cell and the control becomes hidden.

I've even used ONE ComboBox for selections in multiple columns, based on the context of the selected cell.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[LGMan]

Thanks Skip, I'd like to know more about how you get Procedure A and Procedure B to fire on different columns as that is what I think would be useful to achieve in my current workbook and to revisit others that I built previously.

 

[SkipVought]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target.Parent
        If Target.Row = 1 Then Exit Sub     'row 1 headings
        If Target.Count > 1 Then Exit Sub   'multiple cell selection
'assume headings [highlight #FCE94F]Start Date[/highlight] and [highlight #FCE94F]Status[/highlight]
        With .UsedRange
            If Target.Row > .Rows.Count Then Exit Sub   'no rows below table
            
            If Not Intersect(Target, .Rows(1).Find("Start Date").EntireColumn) Is Nothing Then
                ProcedureA
            End If
            
            If Not Intersect(Target, .Rows(1).Find("Status").EntireColumn) Is Nothing Then
                ProcedureB
            End If
        End With
    End With
End Sub

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[LGMan]

Thanks again Skip. I'd not thought of using one worksheet event.

And many thanks for all the helpful replies.