Passing an Object to a Function

[magelan]

Hi People.

So, i'm not sure if this is the way VBA wants me to do this.. but here it is.

global filearr(1 to 4) as string

Private Sub buttonBrowse1_Click() 'file1
    Dim fn As String
    fn = Application.GetOpenFilename _
            (Title:="Open Webroot File 1", MultiSelect:=False) 'open the file
    
    Call outputFileNames(1, tbFileOne, fn) 'call the output function with given parameters
^^^^ TBFILEONE IS AN ACTIVEX TEXTBOX [see the subroutine below]
End Sub


Private Sub outputFileNames(fileCount As Integer, obj As TextBox, filename As String)
    If Len(filename) > 0 Then 'if filename is there...
        fileArr(fileCount) = filename 'set the global variable
        If Len(fileArr(fileCount)) > 50 Then 'if its too big for the box
            Dim splitter() As String
            splitter = Split(fileArr(fileCount), "\") 'split based on \
            Dim i As Integer 'forloop
            Dim max As Integer 'max num of splits to show
            
            If (UBound(splitter) > 3) Then
                max = 3 'if the split path is massive set it to 3
            Else
                max = UBound(splitter) 'otherwise it is 2 or 1
            End If
            obj.Value = "" 'initialize texbox to ""
            
            For i = 0 To max
                obj.Value = obj.Value & splitter(i) 'enter the first half of the path
            Next
            obj.Value = obj.Value & "..." & splitter(UBound(splitter)) '.. and then last entry
        Else
            obj.Value = fileArr(fileCount) 'if its small enough, just output
        End If
    Else
        obj.Value = ""
    End If
End Sub

Basically i want to re-use the code because I'm going to have so many textbox's and browse box's that need this split done, I dont want to copy-paste this code everywhere and worry about scope and variable names..etc.

My problem is that VBA is giving me a type mismatch as marked above in red text. My guess is that VBA doesnt like how I'm passing this textbox to use it dynamically.

Hints?

[Kenneth Hobs]

What kind of textbox? IF a userform, try the type of MSForms.Textbox.

[magelan]

What kind of textbox? IF a userform, try the type of MSForms.Textbox.

I'm not sure what a userform is.. excel labels it as an "activex control". It seems to have worked in the code though. Thanks.

[magelan]

Related topic but new subject --

How do you make these darn text boxes read only? Ive tried tbFileOne.readonly=true and it says Not Supported...

setting Enabled = false makes it grey and ugly.

Protecting the worksheet leaves the textbox able to be edited.

Textbox has been locked since the beginning. Doesnt appear to affect editing.


HALP

[Kenneth Hobs]

Add the Developer ribbon. Double click your activex control to edit the control in the VBE. From the View menu, enable the Properties window. From it, you can see that you need to set the Locked property value to True.

[magelan]

Add the Developer ribbon. Double click your activex control to edit the control in the VBE. From the View menu, enable the Properties window. From it, you can see that you need to set the Locked property value to True.

Coincidentally this had been set to TRue the entire time, Locked property does -nothing- to change my text box, whether true or false nothing changes.

[Kenneth Hobs]

Like cell locking, your sheet must be protected for the ActiveX textbox control locking to take place.

[snb]

if you use
fn = Application.GetOpenFilename("Open Webroot File 1", MultiSelect:=False)

the result can be only 1 filename.
In that case 'fn' is a string.

So in this case the use of the function 'outputfilenames' is nonsense.
This function can only be meaningful if you use:

fn = Application.GetOpenFilename("Open Webroot File 1", MultiSelect:=True)

If you want to make textboxes 'read only' you have chosen the wrong ActiveX control: you'd better use a 'Label' instead.
If you are stubborn in using a textbox and in preventing any userinput in it you can use it's property 'enabled' and set that to 'false'.

[magelan]

if you use
fn = Application.GetOpenFilename("Open Webroot File 1", MultiSelect:=False)

the result can be only 1 filename.
In that case 'fn' is a string.

So in this case the use of the function 'outputfilenames' is nonsense.
This function can only be meaningful if you use:

fn = Application.GetOpenFilename("Open Webroot File 1", MultiSelect:=True)

If you want to make textboxes 'read only' you have chosen the wrong ActiveX control: you'd better use a 'Label' instead.
If you are stubborn in using a textbox and in preventing any userinput in it you can use it's property 'enabled' and set that to 'false'.

I do have multiselect as false, but i'm using the function multiple times so it made sense to me to call it outputfilenames rather than outputfilename because i would be using it to..... output file names!

I am being stubborn yes, because labels dont look the same way textboxes do, and i'm dealing with some very particular users. I'm trying to decide if they will be more upset at the textbox looking like its not selecting a file, or if they will be more upset at the textbox looking like its disabled.

[GTO]

@Kenneth:

Hi Ken,

At least in 2000, with the worksheet protected, setting the Locked property to an ActiveX TextBox seems to have no effect. You can still type in it.

@magelan:

A Label can look just like a TextBox; change the SpecialEffect property to fmSpecialEffectSunken (2).

As a label has a zero internal margin, either add a space to the string's start, of just change alignment to centered.

Hope that helps,

Mark

[Kenneth Hobs]

Mark, try protecting your workbook too. IF those protects don't work in 2000, try closing it an opening it. It should work.

[snb]

see the attachment for 'optimal' userfriendlyness:

[GTO]

Mark, try protecting your workbook too. IF those protects don't work in 2000, try closing it an opening it. It should work.

Hi Ken,

Maybe I am totally glossing over something, but I tried this in 2000 and 2010 (both WIN). I saved the workbook (wb), tried with a protected sheet, tried with a protected sheet and wb, and tried with a protected sheet and protected wb, including 'Windows' protection. Alas - I am able to type in the TextBox to my heart's content.

Changing the .Enabled property to FALSE, as snb shows, of course works, but the control is greyed, which the OP did not seem to desire.

Happy to be corrected, but thus far, I stand by my suggestion of changing a Label control's SpecialEffect to sunken.

Respectfully,

Mark

[Kenneth Hobs]

Locking and protecting the sheets works but only for a short time if at all.

I don't like SendKeys() but sometimes it will suffice. Right click your sheet's tab, View Code, and paste:

Private Sub TextBox1_GotFocus()
  SendKeys "{ESC}", True
End Sub

[snb]

@GTO

An ActiveX Textbox in sheet1, property .locked =true
Worksheet Protect.
In 2010 you can indicate which exceptions you want to make.
In this case I do not select 'edit objects'.

Now I'm not able anymore to enter something into the textbox. (Excel 2010).

[Kenneth Hobs]

That works the first time snb. After you work in another worksheet and set some protection or close and open the workbook, the text can be edited. That is not how it should work.

[snb]

@KH

I would advise not to use any ActiveX controls in this case.