locked
'Sub or Function not defined' RRS feed

  • Question

  • I have exhausted my google-fu on this issue and am sure it's something simple but I can't seem to find what's causing my issue or how to fix it. I have a sub, in a module, that needs to be able to act upon the click event of hundreds of label’s. Most of that works until I get to the point where I need to edit the caption of the calling ‘label’ and the word “Control” is highlighted and the error popup of sub or function not defined comes up.

    A simplified code snippet below.

    Any help would be appreciated!
    Thanks
    Public Sub iCollapseExpandRows(iCallingSheet As String, iCallingRange As Integer, iCallingTag As Integer)
        ‘Dim iTag As Object                                         ‘<<<<  Tried various solutions
        sStartCell = Range("Range_" & iCallingSheet & "_" & iCallingRange).Row
        sStartCell = "B" & sStartCell
       ‘ iTag = Controls("Label" & iCallingTag)    ‘<<<<  Tried various solutions to get “Label201” - "Label" & iCallingTag
        
            ActiveSheet.Unprotect
    ‘   A typical run of this sub would reference “Label201” - "Label" & iCallingTag
            If Controls("Label" & iCallingTag).Caption = "Collapse" Then    <<<<  Sub or Function not defined error with “controls” highlighted
                Controls("Label" & iCallingTag).Caption = "Expand"
                Controls("Label" & iCallingTag).BackColor = RGB(0, 255, 0)
            End if    
    End Sub

    Monday, June 29, 2015 2:08 PM

Answers

  • Sorry - I misunderstood. You need to use an OLEobject:

    Private Sub Label303_Click()
        GLabelClick 303
    End Sub

    Private Sub GLabelClick(i As Integer)
        Dim lab As OLEObject
        Set lab = ActiveSheet.OLEObjects("Label" & Format(i, "000"))  'Assuming a naming convention of 3 digits
        
        If lab.Object.Caption = "Collapse" Then
            lab.Object.Caption = "Expand"
        Else
            lab.Object.Caption = "Collapse"
        End If
    End Sub

    • Marked as answer by JimDango Monday, June 29, 2015 4:42 PM
    Monday, June 29, 2015 3:40 PM

All replies

  • You're not using a control - you are using a shape, and it does not have a caption, it has a textframe and.... it's really confusing.  Anyway, try code like this:

    If ActiveSheet.Shapes("Label" & iCallingTag).TextFrame.Characters.Text = "Collapse" Then ActiveSheet.Shapes("Label" & iCallingTag).TextFrame.Characters.Text = "Expand" Else

    ActiveSheet.Shapes("Label" & iCallingTag).TextFrame.Characters.Text = "Collapse"

    End if



    Monday, June 29, 2015 2:39 PM
  • Hello,

    If I get it correct, this code you mention is in a sub in a module, than where did you declare the variable/collection Controls ?

    I assume you need to adress your labels by using OleObjects instead of Controls. Controls will only work on your form.

    Dim c As OLEObject, ws As Worksheet
       
        Set ws = ActiveSheet
       
        Set c = ws.OLEObjects(1)

    Hope it helps,

    Wouter

    Monday, June 29, 2015 2:50 PM
  • <<Sorry, seems a TAB key submits >>

    Sorry that wasn't clear, what i pasted was the last of my efforts to fix the issue. 

    I started with a spreadsheet with one tab that has 25 "labels" on it to designate some row groupings. 

    The code that manages these row groupings is this:

    Private Sub Label303_Click()

    Dim sStartRow As String Dim sNumRows As String Dim sRange_20 As String Dim sStartCell As String sStartCell = Range("Range_19_25").Row sStartCell = "B" & sStartCell sStartRow = Range("Range_19_25").Row + 1 sNumRows = Range("Range_19_25").Cells(Range("Range_19_25").Cells.Count).Row sRange_20 = sStartRow & ":" & sNumRows RangeValue = 0 Application.ScreenUpdating = False 'Dont allow collapse if data in child rows For i = sStartRow To sNumRows RangeValue = RangeValue + Cells(i, 15).Value RangeValue = RangeValue + Cells(i, 16).Value RangeValue = RangeValue + Cells(i, 17).Value Next i If RangeValue = 0 Then ActiveSheet.Unprotect If Label303.Caption = "Collapse" Then Label303.Caption = "Expand" Label303.BackColor = RGB(0, 255, 0) 'iii = CheckBox19.value If CheckBox19.value <> True Then Rows(sRange_0).Select Selection.EntireRow.Hidden = True Else For ii = sStartRow To sNumRows If Cells(ii, 9).value = 0 Then 'No row value - go ahead and Collapse sRange_0 = ii & ":" & ii Rows(sRange_0).Select Selection.EntireRow.Hidden = True End If Next End If If Not bInProcess Then Range(sStartCell).Select Else Label303.Caption = "Collapse" Rows(sRange_0).Select Selection.EntireRow.Hidden = False Label303.BackColor = RGB(255, 255, 204) If Not bInProcess Then Range(sStartCell).Select End If ActiveSheet.Protect DrawingObjects:=True, contents:=True, Scenarios:=True Else If Not bInProcess Then iRet = MsgBox("These rows can't be collapsed") End If If Not bInProcess Then Range(sStartCell).Select If Not bInProcess Then Application.ScreenUpdating = True End If End Sub

    Except, over time, I have code for each label as this grew slowly over time.

    To make this growth easier to manage, I'm trying to make a single module piece of code that will work for any label instead.  Hence my need to reference specific labels by name.

    When I try to make code that works for this section of code:

    If Label303.Caption = "Collapse" Then Label303.Caption = "Expand" Label303.BackColor = RGB(0, 255, 0)

    is where I get the 'sub or function not defined'.

    I hope that makes more sense.

    Monday, June 29, 2015 3:05 PM
  • In my efforts to convert this worksheet based code to a module based replacement, I have this so far that works to a point:

    Dim sNumRows As String Dim sRange_1 As String Dim sStartCell As String Dim iTag As Object sStartCell = Range("Range_" & iCallingSheet & "_" & iCallingRange).Row sStartCell = "B" & sStartCell iTag = "Label" & iCallingTag iTag = Controls("Label" & iCallingTag) sStartRow = Range("Range_" & iCallingSheet & "_" & iCallingRange).Row + 1 sNumRows = Range("Range_" & iCallingSheet & "_" & iCallingRange).Cells(Range("Range_" & iCallingSheet & "_" & iCallingRange).Cells.Count).Row sRange_1 = sStartRow & ":" & sNumRows RangeValue = 0 Application.ScreenUpdating = False If RangeValue = 0 Then ActiveSheet.Unprotect If Label301.Caption = "Collapse" Then <<<<<<<<<< This is where my conversion attempts run into problems ''I would think I could have used something like iTag = "Label" & iCallingTag but it doesn't work



    Monday, June 29, 2015 3:27 PM
  • Sorry - I misunderstood. You need to use an OLEobject:

    Private Sub Label303_Click()
        GLabelClick 303
    End Sub

    Private Sub GLabelClick(i As Integer)
        Dim lab As OLEObject
        Set lab = ActiveSheet.OLEObjects("Label" & Format(i, "000"))  'Assuming a naming convention of 3 digits
        
        If lab.Object.Caption = "Collapse" Then
            lab.Object.Caption = "Expand"
        Else
            lab.Object.Caption = "Collapse"
        End If
    End Sub

    • Marked as answer by JimDango Monday, June 29, 2015 4:42 PM
    Monday, June 29, 2015 3:40 PM
  • Bernie, you my friend, are a genius!  That worked perfectly! 

    Many thanks!

    Monday, June 29, 2015 4:41 PM