unable to access .text property textbox on worksheet (office 365)

[liw]

Hi all,

Somehow got myself commited to creating a kind of user form in excel (didn't go the 'userform' route, however) I am using activex textboxes (and comboboxes).
I have essentially zero experience and by now I am stumped on how to proceed.

I have a number of boxes (18 - ordered in six 'lines' of three boxes: one to specify a file, one to define a name and one to define a label) that (dis)appear based on the value chosen in a combobox. That works. What I can't get to work is clearing any text entered in these boxes when they are set to invisible.

Here is the code I use to make the optional boxes (dis)appear based on the value entered for a combobox named 'nvarlistmenu'

Private Sub nvarlistmenu_Change()
   
    Dim topvals(6) As Integer
        
    For i = 1 To 6
        topvals(i) = 106 + ((i - 1) * 49.5)
    Next
    
    Dim basenames(3) As Variant    
    basenames(1) = "kernbestandvar"
    basenames(2) = "namevar"
    basenames(3) = "labelvar"
        
   If nvarlistmenu.Value = "nee" Then
        Rows("7:24").EntireRow.Hidden = True
        
        For b = 1 To 3
        For i = 1 To 6
        boxname = basenames(b) & i
        ActiveSheet.Shapes(boxname).Visible = False
        
        Next i
        Next b
        
    ElseIf nvarlistmenu.Value = "ja, 6" Then
        Rows("7:24").EntireRow.Hidden = False
        
        For b = 1 To 3
        For i = 1 To 6
        boxname = basenames(b) & i
        ActiveSheet.Shapes(boxname).Visible = True
        ActiveSheet.Shapes(boxname).Top = topvals(i)
        Next i
        Next b

etc etc.


Now for clearing the boxes: i can't seem to access the .text property in the same manner as the .visible and .top properties.
Something like this:

Private Sub namevar4_Change()
 namevar4.Text = "test"
End Sub

... works perfecty fine to alter the text to 'test' whenever one types in that specific box. But that clearly is not what I am after here.

Within the loops shown above, simply accessing .text doesn't work. Based on info here and elsewhere on the net I suspect I need something along the lines of:

    With ActiveSheet.Shapes(boxname).TextFrame
    .Characters.Text = ""
    End With

Except: I can't figure out precisely what I need. I tried all kinds of combinations of .textframe, .textframe2, .character, .textrange, .text, .value and am flat out of luck so far. Of all the possible things I've tried, the one posted seems to get me the farthest: the error message (error 438: object doesn't support this property or method) is active for the .characters.text line, giving me the impression that at least the "With ActiveSheet.Shapes(boxname).TextFrame"-bit is working/accepted.

If anyone could help me solve this, I'd be super grateful!!

 

[Fluff]

How about like

   With ActiveSheet.OLEObjects(boxname)
      .Visible = False
      .Object.Text = "abc"
   End With

 

[liw]

Wooopdiwoop! It cost me a day and a half but I seem to have figured it out at last!!!

ActiveSheet.Shapes(boxname).OLEFormat.Object.Object.Value = ""

In context:

    ElseIf nvarlistmenu.Value = "ja, 5" Then
        Rows("7:21").EntireRow.Hidden = False
        Rows("22:24").EntireRow.Hidden = True

        For b = 1 To 3
        For i = 1 To 5
        boxname = basenames(b) & i
        ActiveSheet.Shapes(boxname).Visible = True
        ActiveSheet.Shapes(boxname).Top = topvals(i)
        Next i

        For i = 6 To 6
        boxname = basenames(b) & i
        ActiveSheet.Shapes(boxname).OLEFormat.Object.Object.Value = ""
        ActiveSheet.Shapes(boxname).Visible = False
        Next i
        Next b

 

[liw]

Oh Mr (or Ms) Fluff, we just cross posted. Yet yours is way more pleasing to the eye so I'll test and implement if it works (think it should, though perhaps it might need an added .object).

 

[liw]

    ElseIf nvarlistmenu.Value = "ja, 5" Then
        Rows("7:21").EntireRow.Hidden = False
        Rows("22:24").EntireRow.Hidden = True

        For b = 1 To 3
        For i = 1 To 5
        boxname = basenames(b) & i
        
            With ActiveSheet.OLEObjects(boxname)
                .Visible = True
                .Top = topvals(i)
            End With
            
        Next i

        For i = 6 To 6
        boxname = basenames(b) & i
        
            With ActiveSheet.OLEObjects(boxname)
               .Visible = False
               .Object.Value = ""
            End With
        
        Next i
        Next b

[The End]

 

[Fluff]

Glad to help & thanks for the feedback