0

I am using a calendar created by Siddharth Rout found here:How can I create a calendar input in VBA Excel? I have a userform that sits on top of a basic database that has a number of date fields.

I would like to use the one calendar form multiple times, i.e. have a cmdbutton1 on the userform that calls the calendar and puts the date in a label1, and then a cmdbutton2 that calls the same calendar form to populate label2. Obviously these will have different dates.

I have tried to do various forms of temporary binding to variables and nothing has worked. Any help would be appreciated!

Pete_B_C
  • 1
  • 1
    Hi, welcome to SO. As we are not a code writing service, you have to provide us with what you have attempted (i.e. your code) and provide details about the issue you are facing. We can then help to guide you on how to fix your issue. It might be helpful to have a read of: [how to ask a question](https://stackoverflow.com/questions/how-to-ask) – Zac Nov 21 '19 at 13:16
  • Hi Zac, Thanks for the welcome. I read the how to ask a question, however the code that is within Sid's Calendar is quite hard to unpack into a concise question! I'd prefer not to ahve someone write the code for me as I am trying to learn but I don't understand how to temporarily set values in this context. And I don't know where to look! I will attempt to mock up what I am after, but again simple guidance would be preferable. – Pete_B_C Nov 21 '19 at 13:27
  • I think, start from beginning; have you managed to launch Sid's calender? Or is that what you are trying to do? – Zac Nov 21 '19 at 13:37
  • Hi Zac, thanks for your time, launching Sid's calendar and integrating it into a userform that I am using to edit a locked database is totally fine. I can use a cmdbutton1 to launch the calendar, and edit the `MsgBox Label6.Caption, vbInformation, "Date selected"` to be `userform1.label1.text=calendar.label6.text` or some variation. The ssue comes where I want to have multiple cmdbuttons(1,2,3 etc.) to each call up the calendar and populate userform(1,2,3 etc.) respectively. I'm trying to tease out Toms solution below. – Pete_B_C Nov 21 '19 at 16:30
  • Just to clarify, I have a userform1 with userform1.cmdbutton1 that calls up the calendar, and the results are to go back into the userform1.label1, and userform1.cmdbutton2 result to go into userform1.label2 etc. – Pete_B_C Nov 21 '19 at 16:40

2 Answers2

0

You can create instances of the UserForm, set these up when you want and display when you need. From these instances you can modify the child userform

Private Sub CommandButton1_Click()
    Dim ufrm2 As UserForm2

    Set ufrm2 = New UserForm2

    ufrm2.Label1.Caption = Me.CommandButton1.Caption

    ufrm2.Show
End Sub

Private Sub CommandButton2_Click()
    Dim ufrm2 As UserForm2
    Set ufrm2 = New UserForm2

    ufrm2.Label1.Caption = Me.CommandButton2.Caption

    ufrm2.Show
End Sub

Giving:

enter image description here

Tom
  • 9,725
  • 3
  • 31
  • 48
  • Rather then repeating the same code, might be worth having a UDF which initiates the form and shows it and call the UDF from each click – Zac Nov 21 '19 at 14:17
  • @Zac agreed - was more doing it to demonstrate the point that I can use the same `UserForm` and customise it differently. – Tom Nov 21 '19 at 14:46
  • Fare point. I do like the demo though! I'll have to install one of these apps soon – Zac Nov 21 '19 at 14:48
  • 1
    @Zac I use ScreenToGif – Tom Nov 21 '19 at 14:49
  • Thanks. Can't install it on work machine but will do on my laptop.. very handy – Zac Nov 21 '19 at 14:50
  • @Zac I'd check it out - they've got a portable version too (how I use it on my work machine) [ScreenToGif](https://www.screentogif.com/) – Tom Nov 21 '19 at 14:53
  • Nice.. will do. That would be very handy – Zac Nov 21 '19 at 14:57
0

I was looking at this question, and I think I have found the answer @Pete_B_C was looking for. I have added some code to the Userform "frmCalendar" to get the desired result.

'~~> Insert Selected date
Private Sub DTINSERT_Click()
    If Len(Trim(Label6.Caption)) = 0 Then
        MsgBox "Please select a date first", vbCritical, "No date selected"
        Exit Sub
    End If
    *'~~> Change the code here to insert date where ever you want
     If UserForm1.Visible = True Then
        'do something
        UserForm1.TextBox1 = Label6.Caption
    End If
    
    If UserForm3.Visible = True Then
        'do something
        UserForm3.TextBox12 = Label6.Caption
    End If*

     
     Unload Me
End Sub

I have used the option to hide and show the Userforms depending on which Userform is being used. If anyone knows how to do this in a better way than the way I have figured it, please let me know

Ian Martin
  • 41
  • 10