I'm getting an 'object required' error when calling a vba function

Question

I'm getting a 424 "Object Required" when calling a vba function. The function parameter requires a range to change the name of the worksheet tab to be the same as a cell value.

Here is the code:

Dim new_wb As Workbook
Dim act_wb As Workbook
Dim stu_name As Object
Dim lastRow As Long
Dim currCol As Long
Dim nameRange As Range
Set act_wb = ActiveWorkbook
currCol = ActiveCell.Column
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Set nameRange = Worksheets(1).Range("A4")


'begin vba code
ActiveWorkbook.Sheets(1).copy
Worksheet_Change (nameRange) 'error occurs here.

Here is the function signature of the function being called:

Private Sub Worksheet_Change(ByVal Target As Range)

If I remove the 'Set' keyword for nameRange, I get the following error

Change Worksheet_Change (nameRange) to Worksheet_Change nameRange This will take care of the error but why do you want to call the worksheet event like this? — Siddharth Rout
– Siddharth Rout, Commented Apr 18, 2021 at 4:55
What's your suggestion for calling a worksheet event like this? Put it in the relevant sheet code module and WAIT for the event to automaticlly execute :D. I think you need to read about worksheet events and what are they for. — Siddharth Rout
– Siddharth Rout, Commented Apr 18, 2021 at 5:03
@brohjoe To use your general situation as an example think of if you had a table on the first worksheet with 10 named column headers (for whatever) and each of these corresponded to another worksheet with detailed data for that category. If you wanted you could use the Worksheet_Change event to detect when the user edited the contents of one of those cells to update the name of its corresponding sheet automatically. — the VBE - it's right for me
– the VBE - it's right for me, Commented Apr 18, 2021 at 5:11
Actually, I dont' really need the 'Worksheet_Change' function, just a regular function. — brohjoe
– brohjoe, Commented Apr 18, 2021 at 5:29

the VBE - it's right for me · Accepted Answer · 2021-04-18 04:59:22Z

1

As the comment to your OP said, Worksheet_Change is not a standalone procedure you define, but rather an event handler that you declare to provide functionality in response to the Worksheet class raising the Change event behind the scenes. If you would like to change the name shown in a worksheet tab based on a cell value just extract the string from that cell and assign it to the corresponding property of the sheet object:

Dim wsName As String
wsName = Worksheets(1).Range("A4").Value

Dim wS As Worksheet
Set wS = ActiveWorkbook.Sheets(1).Copy
wS.Name = wsName

I am confused why the worksheet and cell references are always the same but you should be able to adjust from there if needed. Happy coding!

answered Apr 18, 2021 at 4:59

the VBE - it's right for me

1687 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

I'm getting an 'object required' error when calling a vba function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related