One Click Event for Multiple Command Buttons 1

[djburnheim]

I have a spreadsheet with a command button for each day of the month. I want the click event for each button to be the same. I also want to reference the cell above which ever command button was pushed. Hopefully you can tell from the code below what I'm trying to do (I know that it is wrong and doesn't work but I hope you can tell what I'm trying to get at)...any suggestions?

Private Sub Test()

Dim Ob As OLEObject
Dim FormLabel As String

For Each Ob In Sheets(1).OLEObjects
If TypeName(Ob.Object) = "CommandButton" Then
With Ob.Object
FormLabel = "Red Alerts for "
frmRedAlerts.Caption = FormLabel & Format(Sheets(1).Cells(1, 2), "d-MMM&quot
frmRedAlerts.Show
End With
End If
Next Ob

End Sub

 

[Zathras]

...I hope you can tell what I'm trying to get at...

No, I can't quite see it. It looks like you are trying to open up a form for each command button on the sheet, but I can't figure out why. It might help if you indicated when the Sub test( ) would be invoked.

As for linking a command button to a cell, AFAIK you can't. But you can put code like this (on the sheet page) for each button:

Option Explicit
Private Sub CommandButton1_Click()
  ProcessClick 1
End Sub
Private Sub CommandButton2_Click()
  ProcessClick 2
End Sub

and put code like this in a module:

Sub ProcessClick(Button As Integer)
  MsgBox "Button click number " & Button
End Sub

Hope this helps.

 

[SkipVought]

Hi,
Each CommandButton has a bunch of events on the worksheet object.

Each CommandButton_Click event should have the following code...

   MyProcedure CommandButton

n

.TopLeft

Then MyProcedure should have a range arguement. Process that range however you like.

Hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

Excel 97 only has .Top and .Left in pixels. No matter, the principle is the same. You still need an event handler for each command button and it should call a common routine passing a parameter to identify itself. I used a simple number. You may find that an identifying string is more useful for your situation.

 

[SkipVought]

Zathras,

97 does have TopLeft as a property of sheet objects. I use it often to position a button or combobox to the position and size of a particular cell...

   With CommandButton1
      .Top = .TopLeft.Top
      .Left = .TopLeft.Left
      .Width = .TopLeft.Width
      .Height = .TopLeft.Height
   End With

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

Ok, good one. I was just looking at the properties sheet. Then assuming you meant

.TopLeftCell

, perhaps djburnheim would prefer to use something like this:

Option Explicit
Private Sub CommandButton1_Click()
  ProcessClick CommandButton1.TopLeftCell
End Sub
Private Sub CommandButton2_Click()
  ProcessClick CommandButton2.TopLeftCell
End Sub

And then this in the module:

Sub ProcessClick(ButtonLocation As Range)
  MsgBox "Button clicked near " & ButtonLocation.Offset(0, 1).Address
End Sub

 

[djburnheim]

Ok...this is the code I have for one of 30 buttons on the sheet, it seems to work OK...from what I understand from everyones posts I'm basically going to have to repeat it for each button. I don't quite understand how to work in Zathras' code above..I want to change the title of the form to display the date from in the cell above the button that was clicked.

***sheet1 code***
Private Sub CmdRedAlerts_Click()
Dim FormLabel As String

FormLabel = "Red Alerts for "
frmRedAlerts.Caption = FormLabel & Format(Sheets(1).Cells(1, 2), "d-MMM&quot
frmRedAlerts.Show
End Sub

***frmRedAlert code***
Private Sub UserForm_Initialize()
txtListProblems.Value = Sheets(2).Cells(1, 1)
End Sub

Private Sub cmdAdd_Click()
Dim RedAlert As String

RedAlert = Sheets(2).Cells(1, 1)
If Sheets(2).Cells(1, 1) = "" Then
RedAlert = txtReported & Chr(32) & "-" & Chr(32) & txtResolved & Chr(32) & txtDetails
Else
RedAlert = RedAlert & Chr(10) & Chr(10) & txtReported & Chr(32) & "-" & Chr(32) & txtResolved & Chr(32) & txtDetails
End If

Sheets(2).Cells(1, 1) = RedAlert
txtReported.Value = ""
txtResolved.Value = ""
txtDetails.Value = ""
txtListProblems.Value = Sheets(2).Cells(1, 1)
cmdAdd.Enabled = False
SetFocus = txtListProblems
End Sub

Private Sub cmdClose_Click()
End
End Sub

Private Sub txtDetails_Change()
cmdAdd.Enabled = True
End Sub

 

[Zathras]

Not clear on how you are coordinating data between two sheets -- I think this is the first time you mentioned Sheeets(2) -- but here is code that is similar to my previous post but you may be able to see the application for your project:

In the Sheet1 code page:

Option Explicit
Private Sub CommandButton1_Click()
  ProcessClick CommandButton1.TopLeftCell
End Sub
Private Sub CommandButton2_Click()
  ProcessClick CommandButton2.TopLeftCell
End Sub
Private Sub CommandButton3_Click()
  ProcessClick CommandButton3.TopLeftCell
End Sub

(As many Subs as you have command buttons.)

In a code module:

Option Explicit
Public RedAlert As String

Sub ProcessClick(ButtonLocation As Range)
  frmRedAlerts!txtListProblems.Value = ButtonLocation.Offset(-1, 0).Text
  frmRedAlerts.Caption = ButtonLocation.Offset(-1, 0).Text
  frmRedAlerts.Show
  ButtonLocation.Offset(-1, 1).Value = RedAlert
End Sub

In the code page for frmRedAlerts:

Private Sub CommandButton1_Click()
  RedAlert = txtListProblems.Value
  Me.Hide
End Sub

Hope this helps!