Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 22nd, 2006, 12:17 PM
Authorized User
 
Join Date: Oct 2006
Posts: 45
Thanks: 0
Thanked 0 Times in 0 Posts
Default How do I reference a txtbox control with variable


I have 12 textboxes on a sheet, named tT1 thru tT12.
I want to create a txtT object variable to refer to them in a loop as follows:

Dim x As Integer
Dim txtT As TextBox
For x = 1 To 12
    set txtT = Evaluate ("tT" & x)
    txtT.Visible = False
Next x

The above does not work. what is the proper way to use a dynamic variable to refer to the textbox objects on the sheet?

Thankyou




 
Old November 22nd, 2006, 01:10 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default

You need to use the OLEObjects collection as follows:

Code:
Sub test()

Dim sh As Worksheet
Dim oOLEObjects As OLEObjects
Dim oOLEObject As OLEObject

    Set sh = ThisWorkbook.Sheets("Sheet1")
    Set oOLEObjects = sh.OLEObjects

    For Each oOLEObject In oOLEObjects
        If Left(oOLEObject.Name, 2) = "tT" Then
            oOLEObject.Visible = False
        End If
    Next oOLEObject

End Sub
 
Old November 22nd, 2006, 03:02 PM
Authorized User
 
Join Date: Oct 2006
Posts: 45
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks maccas, but once my code reaches the "For Each" line it is skipping the all the lines down to the first Next x line. It is doing that in both loops.

Any ideas?

Public Sub ScanTrucksForOrderSheetList()
Dim x As Integer
Dim sh As Worksheet
Dim oOLEObjects As OLEObjects
Dim oOLEObject As OLEObject
Dim boxName As String

DefineWorksheets
LoadTruckPlanArray
OrderSheet_ws.Activate

Set sh = ThisWorkbook.Sheets(1)
Set oOLEObjects = sh.OLEObjects

For x = 1 To TruckCount

For Each oOLEObject In oOLEObjects
    boxName = "tT" & x
    If oOLEObject.Name = boxName Then
        oOLEObject.Visible = True
        oOLEObject.Formula = TrucksFound(x).TruckName
    End If

Next oOLEObject
Next x

For x = TruckCount + 1 To 12
For Each oOLEObject In oOLEObjects
    boxName = "tT" & x
    If oOLEObject.Name = boxName Then
        oOLEObject.Visible = False
    End If

Next oOLEObject
Next x

For x = 1 To TruckCount
Range("EA" & x + 8).Formula = TrucksFound(x).TruckName
Next x

Erase TrucksFound
Erase TruckPlan
End Sub

 
Old November 22nd, 2006, 03:24 PM
Authorized User
 
Join Date: Oct 2006
Posts: 45
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sorry, I got the OLE loops to work...

But how do I assign a value to the text box?

oOLEObject.Value = TrucksFound(x).TruckName

the above doesn't work.

Thanks

 
Old November 23rd, 2006, 04:33 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default

You need to put an intermediary object in the statement as follows

oOLEObject.Object.Value = TrucksFound(x).TruckName

I guess the reason its structured this way is that the OLEObject holds properties and methods that are generic to all types of OLEObject (i.e. not all OLEObjects will have a value property but they all have a Name property). The sub Object interface exposes the full range of methods and properties that the specific instance of the OLEObject type may hold - and so in this case will allow you to get your hands on the TextBox Object you're interested in.

BTW, in your code you have a double loop that is probably unnecessary. You should loop though all OLEObjects and determine the value of x by checking whether the OLEObject.ProgID = "Forms.TextBox.1" and Left(OLEObject.Name,2)="tT". If so then determine x by taking the right hand end of the Name string. This should make you code 12 times quicker (approx).

HTH,
Maccas

 
Old November 28th, 2006, 08:57 PM
Authorized User
 
Join Date: Oct 2006
Posts: 45
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thankyou Maccas,

 I have been meaning to acknowlegde your help and haven't done so. Your explanation definately got put to work, now the forms all run from scratch (ie they are empty in design mode), and I can manage all the OLE objects as I want. Really Cool!

Again,
Thankyou






Similar Threads
Thread Thread Starter Forum Replies Last Post
variable used as cell reference? dabith Excel VBA 3 March 8th, 2014 12:53 AM
Using a variable to reference a cell MikeCt203 Excel VBA 2 March 24th, 2008 04:02 PM
How do I 'Set an obj reference using a variable? TomW Access VBA 1 October 30th, 2006 01:39 PM
Passing the cell reference to a variable Artist Excel VBA 0 April 14th, 2004 04:34 AM
How to reference Public Variable in Code Gino Liu Access VBA 2 June 18th, 2003 01:42 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.