Excel vba function asking for object required error 424

[bilalmussa]

Hi,

I have created the following function in excel

Public Function Main_HideRows(ByVal rng As Range, tgbtn As ToggleButton)

Dim sht As Excel.Worksheet
Dim c As Integer
Dim c2 As Integer

Set sht = Sheet19
Set c2 = 32000

Application.ScreenUpdating = False

' this hides the rows for the range
If tgbtn.Value = False Then
 Debug.Print tgbtn.Value
 tgbtn.Caption = "Show Detail"

For c = 1 To sht.Range("rng").Rows.Count
        If sht.Range("rng").Cells(c, 1).Value = sht.Range("rng").Cells(c, 2).Value = "Visible" Then
            sht.Range("rng").Columns(c).Hidden = False
            If c < c2 Then
                c2 = c
            End If
        Else
            sht.Range("rng").Rows(c).Hidden = True
        End If
    Next

    Application.ScreenUpdating = True
    
    Else
     Debug.Print tgbtn.Value
    Application.ScreenUpdating = False

' this shows the rows for the range

For c = 1 To sht.Range("rng").Rows.Count
        If sht.Range("rng").Cells(c, 1).Value = sht.Range("rng").Cells(c, 2).Value = "Visible" Then
            sht.Range("rng").Columns(c).Hidden = Truee
            If c < c2 Then
                c2 = c
            End If
        Else
            sht.Range("rng").Rows(c).Hidden = False
        End If
    Next

    Application.ScreenUpdating = True
    tgbtn.Caption = "Hide Detail"
    
End If

End Function

I then use the following to call it when the toggle button is clicked

Private Sub tgbtnSeasonal_Click()
Call Main_HideRows(ra_main_seasonals, tgbtnSeasonal)
End Sub

However, I get the 424 error saying that an object is required.

What is the reason behind this ?

Thanks

Bilal

[jaslake]

Hi Bilal

Welcome to the Forum!!

You don't tell us what line of Code is blowing out. Based on the posted Code I'd suggest these changes:

Dim c2 As Long

and

c2 = 32000

[bilalmussa]

Hi,

This is the original sub I wrote for each toggle button and now I want to turn it into a function to make my code cleaner

'Dim sht As Excel.Worksheet
Dim c As Integer
Dim c2 As Integer

'Set sheet19 = Sheet19
c2 = 32000

Application.ScreenUpdating = False

' this hides the rows for the range
If tgbtnTablets.Value = False Then
 Debug.Print tgbtnTablets.Value
 tgbtnTablets.Caption = "Show Detail"

For c = 1 To Sheet19.Range("rg_main_tablets").Rows.Count
        If Sheet19.Range("rg_main_tablets").Cells(c, 1).Value = Sheet19.Range("rg_main_tablets").Cells(c, 2).Value = "Visible" Then
            Sheet19.Range("rg_main_tablets").Columns(c).Hidden = False
            If c < c2 Then
                c2 = c
            End If
        Else
            Sheet19.Range("rg_main_tablets").Rows(c).Hidden = True
        End If
    Next

    Application.ScreenUpdating = True
    
    Else
     Debug.Print tgbtnTablets.Value
    Application.ScreenUpdating = False

' this shows the rows for the range

For c = 1 To Sheet19.Range("rg_main_tablets").Rows.Count
        If Sheet19.Range("rg_main_tablets").Cells(c, 1).Value = Sheet19.Range("rg_main_tablets").Cells(c, 2).Value = "Visible" Then
            Sheet19.Range("rg_main_tablets").Columns(c).Hidden = True
            If c < c2 Then
                c2 = c
            End If
        Else
            Sheet19.Range("rg_main_tablets").Rows(c).Hidden = False
        End If
    Next

    Application.ScreenUpdating = True
    tgbtnTablets.Caption = "Hide Detail"
    
End If

The above works fine with no issues.

Could you advise me on how best to turn this into a function ? The original thread heading was my attempt at writing a function.

Bilal

[Norie]

What are ra_main_seasonals and tgbtnSeasonal?

[bilalmussa]

Hi,

rg_main_seasonals and tgbtnSeasonal are named ranges and toggle button respectively.

I want to turn that sub routine into a function so that I can reuse it for the other toggle buttons on the page by just passing through the named range and toggle button name.

Is that possible ?

Bilal

[Kaper]

First: have you changed

Set c2 = 32000

into

c2 = 32000

As c2 is declared as Integer (it would be wiser to declare it as long) it cannot be Set. You can Set only object variables.

so:

Public Function Main_HideRows(ByVal rng As Range, tgbtn As ToggleButton)

Dim sht As Excel.Worksheet
Dim c As Integer
Dim c2 As Integer

Set sht = Arkusz1
'Set
c2 = 32000
'...

if it does not help, press Debug when code stops, chech which code line is highlited in yellow and let us know.

[bilalmussa]

First: have you changed

Set c2 = 32000

into

c2 = 32000

As c2 is declared as Integer (it would be wiser to declare it as long) it cannot be Set. You can Set only object variables.

so:

Public Function Main_HideRows(ByVal rng As Range, tgbtn As ToggleButton)

Dim sht As Excel.Worksheet
Dim c As Integer
Dim c2 As Integer

Set sht = Arkusz1
'Set
c2 = 32000
'...

if it does not help, press Debug when code stops, chech which code line is highlited in yellow and let us know.

I have made this change. However, I want to turn my sub routine into a function so that I can then reuse it for the other toggle buttons by just passing through the named range and toggle button name.

However, when I try to call it, it says object required.

Any suggestions ?

Bilal

[Kaper]

OK. We know what error message you are getting.
But ... Where the code execution stops?

PS. is rg_main_seasonals the name of the range or range object
I mean: (Ctrl+F3) ... rg_main_seasonals
or

Dim rg_main_seasonals as Range

if it is the first case (just named range) then you shall call

Call Main_HideRows(Range("ra_main_seasonals"), tgbtnSeasonal)

Edit: I noticed after posting (I used parts of your previous messages copied into this answer):
Isn't it just a problem with missspelling:
ra_main_seasonals
vs.
rg_main_seasonals

(the cure for such problems is using Option Explicit to force variable declarations)

[bilalmussa]

Hi,

I made the following change of adding range into the call function and proceeds. Now the code stops here

For c = 1 To sht.Range(rng).Rows.Count

Why is that ? do I not need to write sht.range ?

Bilal

[bilalmussa]

The error I now get is Run time 1004

"Method, 'range' of object '_worksheet' failed"

Why could that be ?

Bilal

[Norie]

Can you post the exact code you have now, and an example of how you are calling it?

[bilalmussa]

Fixed,

I was calling the range into the module which was playing up.

It is

Public Function Main_HideRows(ByVal rng As Range, tgbtn As ToggleButton)

'If rng = Empty Or rng Is Nothing Then
'    MsgBox "Value selection is empty", vbOKOnly, "Error"
'Else
    
Dim sht As Excel.Worksheet
Dim c As Integer
Dim c2 As Long

Set sht = Sheet19
c2 = 32000

Application.ScreenUpdating = False

' this hides the rows for the range
If tgbtn.Value = False Then
 tgbtn.Caption = "Show Detail"

For c = 1 To rng.Rows.Count
        If rng.Cells(c, 1).Value = rng.Cells(c, 2).Value = "Visible" Then
            rng.Columns(c).Hidden = False
            If c < c2 Then
                c2 = c
            End If
        Else
            rng.Rows(c).Hidden = True
        End If
    Next

    Application.ScreenUpdating = True
    
    Else
    
    Application.ScreenUpdating = False

' this shows the rows for the range

For c = 1 To rng.Rows.Count
        If rng.Cells(c, 1).Value = rng.Cells(c, 2).Value = "Visible" Then
            rng.Columns(c).Hidden = Truee
            If c < c2 Then
                c2 = c
            End If
        Else
            rng.Rows(c).Hidden = False
        End If
    Next

    Application.ScreenUpdating = True
    tgbtn.Caption = "Hide Detail"
    
End If

End Function

Thank you very much all !!!

[mikerickson]

You might use code like this

Public Function Main_HideRows(ByVal rng As Range, tgbtn As ToggleButton)
    Dim c As Long
    Dim c2 As Long

    c2 = 32000

    Application.ScreenUpdating = False

    Debug.Print tgbtnTablets.Value
    tgbtnTablets.Caption = IIf(tgbtnTablets.Value, "Hide Detail", "Show Detail")

    With sheet19.Range("rg_main_tablets")
        For c = 1 To .Rows.Count
            .Cells(c, 2).EntireRow.Visible = (CStr(.Cells(c, 2).Value) = "Visible") Xor tgbtnTablets.Value
            If (c < c2) And (CStr(.Cells(c, 2)) = "Visible") Then c2 = c
        Next c
    End With

    Application.ScreenUpdating = True
End Function

Its not clear to me what the rng variable is used for. Perhaps changing the With line to

With rng

would be what you want. Then the range rg_main_tablets would have to be passed to the function as an argument.

Also, its not clear what value you want returned by the function.

(BTW, row visibility cannot be controlled by a UDF called from a worksheet formula, neither can the caption of a toggle button).