Trouble Passing Object Variable within Functions VBA

Question

I have a program that takes user input and places it in a sheet, based on job type. For the purposees of this we're only concerned with Sheet("Active Jobs").

I have a function which takes information from a UserForm, and places it into Cells, based on jobID. A simple count keeps track of the JobID, and places the job into the sheet, based on its ID, as such:

Public btn As Button
Public t As Range

Function GetActiveJob()

Dim i1 As Integer
i1 = Worksheets("Sheet1").Range("C4").Value '//Keeps count of jobs, gives the JobID

  With Worksheets("Active Jobs")
    .Range("A" & (5 * i1)) = UserForm1.TextBox1
    ActiveSheet.Range("B" & (5 * i1) & ":" & "E" & (3 + (5 * i1))).Merge
    .Range("B" & (5 * i1)) = UserForm1.TextBox2  '//Handles a decent bit of text 
    .Range("B" & (5 * i1) & ":" & "E" & (3 + (5 * i1))).VerticalAlignment = xlTop
    Set t = ActiveSheet.Range("G" & (5 * i1))
    Set btn = Worksheets("Active Jobs").Buttons.Add(t.Left, t.Top, t.Width, t.Height)

  With btn
    .Name = "RemovetoArchive" & i1
    .Caption = "Archive"
    .OnAction = "RemovetoArchive"
  End With

  End With

UserForm1.TextBox1.Value = ""
UserForm1.TextBox2.Value = ""

i1 = i1 + 1
Worksheets("Sheet1").Range("C4").Value = i1
MsgBox ("I1 = " & i1) 'For testing purposes

End Function

(The job ID needn't be unique)

As you can see, the userform creates a button with a name (Name of the function) followed by the jobID.

When a job is complete, & the archive button is pressed, I want to move it into a new sheet, called Archive.

To do this I take the string and manipulate it to get the ID Number, then convert the string to integer:

  Function RemovetoArchive()

    Dim ButtonID As Integer
    Dim ButtonString As String

    ButtonString = Mid(btn.Name, 16, 2)  '<-- Error here, (see below)
    ButtonID = CInt(ButtonString)

    Worksheets("Active Jobs").Range("A" & (5 * ButtonID) & ":R" & (3 + (5 * ButtonID))).Select


    MsgBox ("ButtonID is " & ButtonID) 'Testing

The ID Number is used as a variable to determine which range needs to be selected, and moved to archive.

Running this however, I get

Object variable or With Block Variable not set

I'm having issues with setting the btn.Name object. i.e.

I enter a job with JobID '5'
The information is created along with a button.
I press Button 5, a.k.a "RemovetoArchive5"

My code should take just the numeric character from the string "RemovetoArchive5" and convert it to integer, to be used as ButtonID.

As it is now, btn.Name, doesn't have the value

btn.Name = "RemovetoArchive5"

Will this have to be done in the GetActiveJobs Function? How can I pass the btn.Name Object, after Clicking the button it refers to?

Any help would be greatly appreciated.

The term "passing object variable to a function" typically implies parameters, and your functions have none. Did you read up on parameters? btn seems to be a module-level variable, aka "public field" or "global variable", which is the opposite of "passing parameters" — Mathieu Guindon
– Mathieu Guindon, Commented Mar 8, 2017 at 16:34
@SJR That provides me the same results as what I have now, through testing, and is perhaps more logical. I think the issue I'm having is that, when calling the "RemovetoArchive" Function, the btn.Name property isn't set, i.e. it has no value, and thus cannot manipulate the string. — Thom G
– Thom G, Commented Mar 8, 2017 at 16:36
If the error is run-time error 91, then it's not the Name property that has no value, it's the btn reference itself; btn is Nothing. Which type of module is btn declared in? A standard/procedural module? A worksheet/class module? A userform's code-behind? See Variables and Scopes in the VBA topics of Documentation.SO. — Mathieu Guindon
– Mathieu Guindon, Commented Mar 8, 2017 at 16:38
I deleted my comment because when I read @Mat'sMug's comment I realised I hadn't read your question properly. You need to address the issues he raises (though my suggestion should help in the end). Why does your function need to add the button and define its properties each time it's called? — SJR
– SJR, Commented Mar 8, 2017 at 16:53
Also, specify Option Explicit at the top of every module - I suspect btn is undeclared/inaccessible from the module you're trying to access it from. — Mathieu Guindon
– Mathieu Guindon, Commented Mar 8, 2017 at 16:55

YowE3K · Accepted Answer · 2017-03-09 01:04:14Z

Using the name of a temporary object that you created when you created the button can, at best, return you the name of the last one you created - and even that won't be possible once that temporary object is garbage-collected.

So, if you want to find the Name of the button that invoked RemoveToArchive, you should do something like the following, which uses the Application.Caller to determine which button was pressed:

Sub RemovetoArchive()  'Only use Function when you have a function,
                       'use Sub when you have a subroutine
    Dim ButtonID As Integer
    Dim ButtonString As String

    ButtonString = Mid(Application.Caller, 16, 2)
    ButtonID = CInt(ButtonString)

    Worksheets("Active Jobs").Range("A" & (5 * ButtonID) & ":R" & (3 + (5 * ButtonID))).Select

    MsgBox ("ButtonID is " & ButtonID) 'Testing

End Sub

A subsequent comment to the question by Mark Fitzgerald also made me see that there is a potential problem in your GetActiveJob code. The section of your code which says:

With Worksheets("Active Jobs")
  .Range("A" & (5 * i1)) = UserForm1.TextBox1
  ActiveSheet.Range("B" & (5 * i1) & ":" & "E" & (3 + (5 * i1))).Merge
  .Range("B" & (5 * i1)) = UserForm1.TextBox2  '//Handles a decent bit of text 
  .Range("B" & (5 * i1) & ":" & "E" & (3 + (5 * i1))).VerticalAlignment = xlTop
  Set t = ActiveSheet.Range("G" & (5 * i1))
  Set btn = Worksheets("Active Jobs").Buttons.Add(t.Left, t.Top, t.Width, t.Height)

  With btn
    .Name = "RemovetoArchive" & i1
    .Caption = "Archive"
    .OnAction = "RemovetoArchive"
  End With

End With

has a few references to ActiveSheet. If the active sheet isn't the "Active Jobs" worksheet then that will cause problems. (But it will obviously work if you always ensure that the active sheet is "Active Jobs".) To avoid problems, I suggest you change that code to:

With Worksheets("Active Jobs")
  .Range("A" & (5 * i1)) = UserForm1.TextBox1
  .Range("B" & (5 * i1) & ":" & "E" & (3 + (5 * i1))).Merge
  .Range("B" & (5 * i1)) = UserForm1.TextBox2  '//Handles a decent bit of text 
  .Range("B" & (5 * i1) & ":" & "E" & (3 + (5 * i1))).VerticalAlignment = xlTop
  Set t = .Range("G" & (5 * i1))
  Set btn = .Buttons.Add(t.Left, t.Top, t.Width, t.Height)

  With btn
    .Name = "RemovetoArchive" & i1
    .Caption = "Archive"
    .OnAction = "RemovetoArchive"
  End With

End With

This solved it, and thank you for the additional information. The jobs get placed onto sheets determined by the value of one of three option buttons. So I was going to do something like: If optionbutton2.value = true then Sheet2.activate for example, but your suggestion is definitely better imo. Thank you!

Collectives™ on Stack Overflow

Trouble Passing Object Variable within Functions VBA

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related