UserForm with multiple command buttons

[clouddog9]

I have a form that has multipe command buttons on it. Instead of making 40-50 commandbuttonx_Click subs (the form is a calendar), is there a way to loop through the buttons to see which one was pressed? I recently thought of using togglebuttons, but I am open to suggestions.

 

[SkipVought]

Hi,

What are each of these buttons to do: 50?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[clouddog9]

Skip - you work weekends too?

The buttons' values change depending on which month and year are selected. What I want to have happen is the user click to on a button, that contains a day, the date (in a specific format) is placed in cell... says "A1" for the sake of this discussion. I hope this illustrates my desire to not have multiple click subs in the code.

I would post the code (nothing business senstive), but my employer blocks mediafire.

 

[SkipVought]

Then you ought to be able to use ONE button, and use the selected parameters.

I am building retail store display cabinets for our Infant & Toddler resale shoppe. Browsing TT during lunch, just for fun.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[clouddog9]

Could you give me a code for an example? I am not sure I am following.

 

[SkipVought]

Sub OneButton_Click()
   DoSomething SelectedDate
End Sub

Sub DoSOmething(dte as date)
   'depending on the date selected, do what is required here
End sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[clouddog9]

I found this site that I think will do what I want. Is this what you were talking about?

http://www.j-walk.com/ss/excel/tips/tip44.htm

 

[SkipVought]

If you had buttons that did different things, then yes.

But you have umpteen buttons that all do the same thing except for a date parameter, if I understand what you previously stated. So what's the need for separate buttons?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[clouddog9]

I was trying to make a calendar. After doing some research I found out that VBA already has the control built in. I almost wanted to kick my self, but then discovered that my employer has that option blocked (not sure why). Skip, I do appreciate the help. Luckily I learned something new.

 

[fumei]

True, there is a calendar, but the essential point of Skip's question remains.

Although of course we can not see what you are doing, it is most likely true that you do not need multiple buttons. One button taking in parameters will most likely work.

In other words (and again we can not see what you have), clicking on a "date" may pass that date to the commandbutton.

However, if the date is some cort of control itself, then you would have to have individual procedures to do the passing.

Bottom line is, as Skip points out, that if you have a procedure (your _Click event) that doing the SAME thing, but with different values, then have that value as a parameter.


unknown

 

[MajP]

Clouddog9,
If I understand you want to build your own "calendar control" form. I have seen many versions of this done in Access and you can google that. You can probably google an MSFORMS version as well, but I have not come across it. Not sure how your employer can deny access to the MS calendar control, but building your own calendar can avoid some versioning issues. Normally it takes 42 controls (7 x 6) to build the calendar days. You hide the ones you do not need and fill in the captions for each day. When the form loads or month/year changes you have to load the days. I would use labels instead of a command buttons because it gives a little more formatting, and change the effect to sunken when you click on a label representing a day. But like you showed, I would use a class module to capture the form's events, and then you would only have to build a single event to capture clicking on any of the 42 labels. However, this takes some understanding of class modules and working with capturing events. If you are not familiar it may just be faster to make the 42 events.

Although this is from an access version the idea is similar to show/hide the labels and fill with day values.

Private Function ShowCal() As Boolean
On Error GoTo Err_Handler
    'Purpose:
    Dim dtStartDate As Date     'First of month
    Dim iDays As Integer        'Days in month
    Dim iOffset As Integer      'Offset to first label for month.
    Dim i As Integer            'Loop controller.
    Dim iDay As Integer         'Day under consideration.
    Dim bShow As Boolean        'Flag: show label
    
    dtStartDate = Me.txtDate - Day(Me.txtDate) + 1  'First of month
    iDays = Day(DateAdd("m", 1, dtStartDate) - 1)   'Days in month.
    iOffset = Weekday(dtStartDate, vbSunday) - 2    'Offset to first label for month.
    
    For i = 0 To 41
        With Me("lblDay" & Format(i, "00"))
            iDay = i - iOffset
            bShow = ((iDay > 0) And (iDay <= iDays))
            If .Visible <> bShow Then
                .Visible = bShow
            End If
            If (bShow) And (.Caption <> iDay) Then
                .Caption = iDay
            End If
        End With
    Next
    
    Call ShowHighligher("lblDay" & Format(Day(Me.txtDate) + iOffset, "00"))
    
Exit_Handler:
    Exit Function

Err_Handler:
    Call LogError(Err.Number, Err.Description, conMod & ".ShowCal")
    Resume Exit_Handler
End Function