Referencing command button from active sheet

[sak1985]

Hello,

Need help in the below code. I am calling a command button sub procedure using the loop. When I did it without loop I used Sheet3.CommandButton3_Click and it worked fine. But, then I am doing it using loop then I change it to ActiveSheet.CommandButton3_Click which is throwing error. wsa is the worksheet object and I don't know how to call the command button procedure using activesheet or anyother way?

cd = 1
  ci = 10
     For Each wsa In ThisWorkbook.Worksheets
       
     If Left(wsa.Name, 2) = Format(cd, "00") Then
      in1 = "Sheet" & wsa.Index
     wsa.Activate
     ActiveSheet.Range("R2").Value = "'" & Sheet4.Range("E8").Value
     ActiveSheet.Range("R3").Value = Sheet4.Range("B" & ci).Value
     'Call Sheet3.CommandButton3_Click
     Call ActiveSheet.CommandButton3_Click
   
   cd = cd + 1
   ci = ci + 1
    End If
   Next wsa

 

[Jeffrey Mahoney]

I suggest creating a SUB in a standard module and calling the macro from both the command button.

SO, if you named your macro as such

SUB Code_For_CommandButtn3
  'Some code here
END SUB

Then you would add the line 'Code_For_CommandButtn3' in the command button SUB and in the code above.

Jeff

 

[sak1985]

Thank you Jeff,

I have tried the above solution and it looks good under certain conditions.

My other question is when ever the new sheet is active the button in that active sheet needs to be called. If you see when I do this as a static call using

 call Sheet2.CommandButton3_click

This works good an when you type Sheet2 automatically all the properties and the buttons in that sheet gets populated. But, when I use Activesheet command I couldn't see the objects (buttons) in that sheet rather I get only the properties like range, cells, active etc.
So, because of this I couldn't complete the code.

How can I call the button dynamically when I open multiple sheet in a loop?

For Each wsa In ThisWorkbook.Worksheets
       
     If Left(wsa.Name, 2) = Format(cd, "00") Then
      in1 = "Sheet" & wsa.Index
     wsa.Activate
     ActiveSheet.Range("R2").Value = "'" & Sheet4.Range("E8").Value
     ActiveSheet.Range("R3").Value = Sheet4.Range("B" & ci).Value
     'Call Sheet3.CommandButton3_Click ' This works good as I reference it to sheet3 and I get the buttons created in that sheet
     Call ActiveSheet.CommandButton3_Click ' This is not working and I need to know how I can call this commandbutton when I open multiple sheet in loop
   
   cd = cd + 1
   ci = ci + 1
    End If
   Next wsa

Sakthi

 

[Jeffrey Mahoney]

As I said in my previous post. You need to create a SUB in a standard module. A standard module starts off named Module1 or Module2.

If you don't have a standard module yet, add one. Then make a SUB called Code_For_CommandButtn. Copy all the code you have in Sheet3.CommandButton3_Click and paste it into the new SUB. Then make all your calls like Module1.Code_For_CommandButtn

SUB Code_For_CommandButtn
  'Some code here
END SUB

Please post the code you have in CommandButton3_Click. Do you have different code in each CommandButton for each sheet or are they all the same?

Jeff

 

[sak1985]

I am providing the code here
This code is in commandbutton3 which is in every sheets and it works fine when I click the button on that specific sheet.
commandbutton3 which triggers the below code.

Public Sub CommandButton3_Click()
Dim ins1, ins2 As Long
 Dim inlnpl, inlnpl1 As Variant
 ins1 = ActiveSheet.Range("A" & ActiveSheet.Range("A7").End(xlDown).Row).Row
 'ActiveSheet.Range("A" & ins1).EntireRow.Insert
 ActiveSheet.Range("A" & ins1 + 1).Value = "'" & ActiveSheet.Range("R2").Value 'Date is added
 ActiveSheet.Range("B" & ins1 + 1).Value = ActiveSheet.Range("R3").Value ' Value is added
 ActiveSheet.Range("C" & ins1 + 1).Value = ActiveSheet.Range("C" & ins1).Value ' copying the I mean
 ActiveSheet.Range("G" & ins1 + 1).Value = ActiveSheet.Range("G" & ins1).Value ' copying the MR mean
 ActiveSheet.Range("D" & ins1 + 1).Value = Abs(ActiveSheet.Range("B" & ins1 + 1).Value - ActiveSheet.Range("B" & ins1).Value)
 
 Dim addindu1 As Long
addindu1 = ActiveSheet.Range("C" & ins1 + 1).Value + ((2.66 * ActiveSheet.Range("G" & ins1 + 1).Value))
If addindu1 < 1 Then
ActiveSheet.Range("E" & ins1 + 1).Value = ActiveSheet.Range("C" & ins1 + 1).Value + ((2.66 * ActiveSheet.Range("G" & ins1 + 1).Value))
Else
ActiveSheet.Range("E" & ins1 + 1).Value = 1#
End If
 

 'ActiveSheet.Range("E" & ins1 + 1).Value = ActiveSheet.Range("C" & ins1 + 1).Value + ((2.66 * ActiveSheet.Range("G" & ins1 + 1).Value))
 inlnpl = ActiveSheet.Range("C" & ins1 + 1).Value - ((2.66 * ActiveSheet.Range("G" & ins1 + 1).Value))
 inlnpl1 = Application.WorksheetFunction.Max(inlnpl, 0)
 ActiveSheet.Range("F" & ins1 + 1).Value = inlnpl1
 ActiveSheet.Range("H" & ins1 + 1).Value = ((3.27 * ActiveSheet.Range("G" & ins1 + 1).Value))
 ActiveSheet.Range("I" & ins1 + 1).Value = ActiveSheet.Range("I" & ins1).Value
 Call afteradd
End Sub

But, I am facing issue here, I want to trigger the above commandbutton from the comman page and it has the below code. It has a for loop where it referes the sheet which is opened based on the condition (if sheet name starts with 1 then the condition is true and that sheet will be activated) and it has the commandbutton3 which needs to be triggered from the loop.
If I do it without a for loop by replicating the below code to the numer of sheet's then I can refer as sheet1.commandbutton3, sheet2.commandbutton3 etc... which is working. But, when I refer it as activesheet.commandbutton3 the throws error.

Public Sub adddata()
If Sheet4.Range("B10").Value = "" Or Sheet4.Range("B11").Value = "" Or Sheet4.Range("B11").Value = "" Then
  MsgBox "Some of the Values are not entered"
 Else
 
  Dim ci, cd, ce As Long
  Dim in1 As Variant
  Dim wsa As Worksheet
  cd = 1
  ci = 10
     For Each wsa In ThisWorkbook.Worksheets
       
     If Left(wsa.Name, 2) = Format(cd, "00") Then
      in1 = "Sheet" & wsa.Index
     wsa.Activate
     ActiveSheet.Range("R2").Value = "'" & Sheet4.Range("E8").Value
     ActiveSheet.Range("R3").Value = Sheet4.Range("B" & ci).Value
     'Call ActiveSheet.code_comm3 - This command is for triggering the above commandbutton3. So here this code refers the active sheet it can be Sheet1 or sheet2 or sheet3.
     
   
   cd = cd + 1
   ci = ci + 1
    End If
   Next wsa
   
   End If
End Sub

 

[Jeffrey Mahoney]

Now we're getting somewhere

Change all the CommandButton SUBS to only contain that one line. Add the Code_For_CommandBtn SUB to a standard module.

The cool part about having all the code in one place is so you don't have to make changes to every single sheet/Button code. We just sent the worksheet as an object over to the CommandBtn code.

All 3 of these SUBS have changed so please copy all of them.

You make a call to afteradd. What is that? We may need to make changes to that also. Please post that code.

Public Sub CommandButton3_Click()
  Call Code_For_CommandBtn(ActiveSheet)
End Sub

Sub Code_For_CommandBtn(aSht As Worksheet)
  Dim ins1, ins2 As Long
  Dim inlnpl, inlnpl1 As Variant
  
  ins1 = aSht.Range("A" & aSht.Range("A7").End(xlDown).Row).Row
  'aSht.Range("A" & ins1).EntireRow.Insert
  aSht.Range("A" & ins1 + 1).Value = "'" & aSht.Range("R2").Value 'Date is added
  aSht.Range("B" & ins1 + 1).Value = aSht.Range("R3").Value ' Value is added
  aSht.Range("C" & ins1 + 1).Value = aSht.Range("C" & ins1).Value ' copying the I mean
  aSht.Range("G" & ins1 + 1).Value = aSht.Range("G" & ins1).Value ' copying the MR mean
  aSht.Range("D" & ins1 + 1).Value = Abs(aSht.Range("B" & ins1 + 1).Value - aSht.Range("B" & ins1).Value)
  
  Dim addindu1 As Long
  addindu1 = aSht.Range("C" & ins1 + 1).Value + ((2.66 * aSht.Range("G" & ins1 + 1).Value))
  If addindu1 < 1 Then
    aSht.Range("E" & ins1 + 1).Value = aSht.Range("C" & ins1 + 1).Value + ((2.66 * aSht.Range("G" & ins1 + 1).Value))
  Else
    aSht.Range("E" & ins1 + 1).Value = 1#
  End If
  
  
  'aSht.Range("E" & ins1 + 1).Value = aSht.Range("C" & ins1 + 1).Value + ((2.66 * aSht.Range("G" & ins1 + 1).Value))
  inlnpl = aSht.Range("C" & ins1 + 1).Value - ((2.66 * aSht.Range("G" & ins1 + 1).Value))
  inlnpl1 = Application.WorksheetFunction.Max(inlnpl, 0)
  aSht.Range("F" & ins1 + 1).Value = inlnpl1
  aSht.Range("H" & ins1 + 1).Value = ((3.27 * aSht.Range("G" & ins1 + 1).Value))
  aSht.Range("I" & ins1 + 1).Value = aSht.Range("I" & ins1).Value
  Call afteradd
End Sub

Public Sub adddata()  
If Sheet4.Range("B10").Value = "" Or Sheet4.Range("B11").Value = "" Or Sheet4.Range("B11").Value = "" Then
    MsgBox "Some of the Values are not entered"
  Else
 
    Dim ci, cd, ce As Long
    Dim in1 As Variant
    Dim wsa As Worksheet
    cd = 1
    ci = 10
    For Each wsa In ThisWorkbook.Worksheets
      If Left(wsa.Name, 2) = Format(cd, "00") Then
       in1 = "Sheet" & wsa.Index
        'wsa.Activate
        wsa.Range("R2").Value = "'" & Sheet4.Range("E8").Value
        wsa.Range("R3").Value = Sheet4.Range("B" & ci).Value
        
        Call Code_For_CommandBtn(wsa)
     
        cd = cd + 1
        ci = ci + 1
      End If
    Next wsa
   
  End If
End Sub