VBA how to use a dynamic sheetname within the sub

quarna

New Member
Joined
Oct 25, 2021
Messages
38
Office Version
  1. 365
Platform
  1. Windows
Hi

VBA Code:
Option Explicit

Sub button1_click()

    Call test

End Sub

Sub button2_click()

    Call test

End Sub

Private Sub test()

    Ark1.Range("A1").Value = "Box1"
    
End Sub

Can i use the two buttons to run test sub, and when button1 is pressed then "ark1" is used, but when button2 is pressed then i want to reference sheet "ark2".
But i want to use the same code.

So can i replace ark1 with ark2 within the code somehow.?

This is a simplified version of what im working with but this is in essence my issue. Maybe it can be done with a function or something.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,325
You can write the test sub to take an argument.

VBA Code:
Sub Test(sheetName as String)
    ThisWorkbook.Sheets(sheetName).Range("A1").Value = "Box 1"
End Sub

Sub Button1_Click()
    Call test("ark1")
End Sub

Sub Button2_Click()
    Call test("ark2")
End Sub
 
Upvote 0
Solution

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
2,042
Office Version
  1. 2016
With sheet1 & sheet2 (With ark1 or ark2 do the same)
VBA Code:
Public num As String
Sub Button1_Click()
num = Replace(ActiveSheet.Buttons("button 1").Text, "Button ", "")
test num
End Sub
Sub Button2_Click()
num = Replace(ActiveSheet.Buttons("button 2").Text, "Button ", "")
test num
End Sub
Sub test(num As String)
Sheets("Sheet" & num).Activate
ActiveSheet.Range("A1").Value = "Box" & num
End Sub
 
Upvote 0

quarna

New Member
Joined
Oct 25, 2021
Messages
38
Office Version
  1. 365
Platform
  1. Windows
You can write the test sub to take an argument.

VBA Code:
Sub Test(sheetName as String)
    ThisWorkbook.Sheets(sheetName).Range("A1").Value = "Box 1"
End Sub

Sub Button1_Click()
    Call test("ark1")
End Sub

Sub Button2_Click()
    Call test("ark2")
End Sub

Amazing, thanks alot.
What a cool thing to pass arguments in the sub, learned something new.. :)
 
Upvote 0

Forum statistics

Threads
1,186,381
Messages
5,957,530
Members
438,310
Latest member
excelvolution

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top