Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations dencom on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Passing Array value in textbox. The textbox turns up empty.

Status
Not open for further replies.

vbcoder2012

Programmer
Feb 4, 2007
8
US
I am passing the value of an array to a textbox, but it always turns up empty. When i pass the same value to an excel cell, i get the value. The function calculates the value of USCityA

I am giving parts of the code below:
Redim Pline(9)
Pline(9) = USCityA
test = "Hello"
USA = test & Pline(9)

In another sub, i call this funtion above to allocate the value of USC to the city as shown below:

CityA = USA

Activesheet.OLEobjects("txtbCityA").Object.Value = "CityA:" & CityA


The value i get in my textbox is only "Hello". When i pass USA to an excel cell, i get "Hello Fortworth" (or whatever the value of "USA" is calculated in the function)
 
When you code:
Code:
Redim Pline(9)
Pline(9) = USCityA
Redim Pline(9)
you will have 9 elements, from 0 to 8
Pline(9) = USCityA
should give you an error "Subscript out of range" unless your error handler of VB settings allow this eror to go thru.


Have fun.

---- Andy
 
>Redim Pline(9) ... you will have 9 elements, from 0 to 8

Are you quite, quite, quite sure?
 
I was, but I am not any more.

Redim Pline(9) will have 10 elements, from 0 to 9

Sorry for the confusion.

Have fun.

---- Andy
 
Hi all,

Pline(9) is an array with 10 elements, and every element is allocated a value

Pline(1) = "the" & routingsegment
.
.
.
Pline(9) = USCityA

My problem is that when i allocate this
Pline(9) = USCityA
test = "Hello"
USA = test & Pline(9)

City = USA

then i only get "Hello" in the textbox, whereas in a excel cell, i get the entire value of USA that is "Hello Fortworth"

USCityA is a variable, it changes everytime the application is run, and depends on what city the user inputs.


I have been stuck on this for quite sometime now, and cant seem to figure this out.

 
JoeatWork",

Even if i switch places, and write Pline(9) first and then test,

USA = Pline(9) & test

City = USA


the excel cell shows "Fortworth Hello", but in the textbox, i still get "Hello"

USA = test & Pline(9)

City = USA

So thats how i know that the value is not coming out in the textbox, and it is not being truncated either...

Any more ideas guys, i would really appreciate some help..

thanks,
 
You haven't really given us enough info to work with, I'm afraid.

You don't show how you are setting the Excel cell, nor is it clear where you are setting the textbox. Is 'City' a textbox? Or is it just another variable?
 
Public Function UT(Segment, Bandwidth, P_Lline, Mult, USA)

Dim r1 As Range, r2 As Range, r3 As Range, myMultiAreaRange As Range
Worksheets("NP").Activate
Selection.AutoFilter
Set r1 = Range("a1:a50000")
Set r2 = Range("j1:j50000")
Set r3 = Range("s1:s50000")
Set myMultiAreaRange = Union(r1, r2, r3)

ReDim P_Lline(9)

PointA = Mid(Segment, 9, 7)
pointZ = Right(Segment, 7)
With myMultiAreaRange
Set FC = .Find(what:=PointA, LookIn:=xlValues)
If FC Is Nothing Then
MsgBox "alert", vbCritical, "CH Lookup"
Exit Function
End If
End With
FC.Select
adrsX = FC.Row
adrsY = FC.Column
USPoPA_NpaNxx = Cells(adrsX, adrsY)
USPoPA_street = Cells(adrsX, adrsY + 4)
USPoPA_city = Cells(adrsX, adrsY + 5)
With myMultiAreaRange
Set FC = .Find(what:=pointZ, LookIn:=xlValues)
If FC Is Nothing Then
MsgBox "alert", vbCritical, "CH Lookup"
Exit Function
End If
End With
FC.Select
adrsX = FC.Row
adrsY = FC.Column
USPoPZ_NpaNxx = Cells(adrsX, adrsY)
USPoPZ_street = Cells(adrsX, adrsY + 4)

P_Lline(0) = "US segment between " & USPoPA_street & " and " & USPoPZ_street

P_Lline(1) = Segment

Dim test As String
P_Lline(9) = USPoPA_city
test = "Hello"
USA = test & P_Lline(9)

' i cant ptovide all the arrays here, its a very long code

End Function

--------------------------------------------------------
Public Sub Vert(Segment, Bandwidth, Pnl_Col)
Call UT(Segment, Bandwidth, P_Lline, 1)
Worksheets("expo").Select
ActiveSheet.Shapes("pct1").Select
Selection.Copy
ActiveSheet.Cells(1, 7 + Pnl_Col).Select
ActiveSheet.Paste
With ActiveSheet

' this is where all the arrays values are exported on the excel cells and P_Lline(9) comes out on row 175 column 7.

MyArray = Array(2, 7, 9, 13, 35, 42, 47, 67, 74, 175)
For n = 0 To 9
.Cells(MyArray(n), 7 + Pnl_Col).Value = P_Lline(n)
Next n
End Select
End With
End Sub

-----------------------------------------------------------


'in another sub, i assign these values, the code is to big to fit here. CntryA ad CityA etc are all variables, they change everytime the model is run


CntryA = ActiveSheet.Cells(2, 7).Value
PopA = ActiveSheet.Cells(2, 4).Value
PopZ = ActiveSheet.Cells(2, 8).Value
CityZ = ActiveSheet.Cells(2, 9).Value
If CntryA = "US (US)" Then
Call UT(Segment, BandWidth, P_Lline, USA, Mult)
CityA = USA
Else
CityA = ActiveSheet.Cells(2, 5).Value
End If

----------------------------------------------------------


Public Sub Design(PopA, PopZ, CityA, CityZ)
Application.CutCopyMode = False
If Not WorksheetExists("Expo") Then
Sheets("Template").Copy Before:=Worksheets("Title")
ActiveSheet.Name = "Expo"
Else

Sheets("Expo").Columns("H:Z").Delete
End If
Sheets("Expo").Activate
Sheets("Expo").Range("A1").Select
Application.CutCopyMode = False
ActiveSheet.OLEObjects("txtbPoPA").object.Value ="Pop A: " & PopA
ActiveSheet.OLEObjects("txtbPoPZ").object.Value ="Pop Z: " & PopZ)
ActiveSheet.OLEObjects("txtbCityA").object.Value = "City A: " & CityA
ActiveSheet.OLEObjects("txtbCityZ").object.Value = "City Z: " & CityZ

End Sub
 
This is the code i have, i cant provide all the code, its just to big to fit here...but these are parts. So you see, i'm trying to get the value of USA in City, that should come out in the textbox, but all i get is "hello". where as in the excel cell i get "Hello Fortworth".

What can the problem be?
 
1) Use Option Explicit
2) Declare your variables

Doing the above will make debugging much easier for you - and particularly for anyone else you ask to look at the code ...

3) VBA questions are often better posed in forum707

You don't think it might have something to do with the fact that sometimes you call UT like:

Call UT(Segment, BandWidth, P_Lline, USA, Mult)

and sometimes like

Call UT(Segment, BandWidth, P_Lline, Mult, USA)


 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top