PDA

View Full Version : Passing an Object to a Function



magelan
10-15-2012, 01:29 PM
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
10-15-2012, 01:53 PM
What kind of textbox? IF a userform, try the type of MSForms.Textbox.

magelan
10-15-2012, 01:59 PM
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
10-15-2012, 02:48 PM
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
10-15-2012, 03:49 PM
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
10-19-2012, 07:18 AM
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
10-19-2012, 08:30 AM
Like cell locking, your sheet must be protected for the ActiveX textbox control locking to take place.

snb
10-20-2012, 09:11 AM
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
10-20-2012, 10:57 AM
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
10-20-2012, 03:17 PM
@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
10-20-2012, 07:40 PM
Mark, try protecting your workbook too. IF those protects don't work in 2000, try closing it an opening it. It should work.

snb
10-21-2012, 04:41 AM
see the attachment for 'optimal' userfriendlyness:

GTO
10-22-2012, 03:36 AM
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
10-22-2012, 06:13 AM
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
10-22-2012, 06:16 AM
@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
10-22-2012, 06:44 AM
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
10-22-2012, 12:21 PM
@KH

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