473,326 Members | 2,126 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Accessing array of controls in EXCEL2003 using VBA

1
I am adding run time generate controls in array. Now I want to retrieve those controls in other subroutine in same form. But when I am tring to retrieve control it says "Object variable or with variable not set". It seems like at the time of adding controls, there is mistake.
AnyOne can help me? I am stuck since yesterday. Here is code for what I am trying.

Expand|Select|Wrap|Line Numbers
  1. Private myControlArray() As Control
  2. Private userPromtedValue(50) As String
  3.  
  4. Public Property Get PublicUserPromtedValue(ByVal Index As Long) As String
  5.   PublicUserPromtedValue = userPromtedValue(Index)
  6. End Property
  7.  
  8. Public Property Let PublicUserPromtedValue(ByVal Index As Long, ByVal NewValue As String)
  9.   userPromtedValue(Index) = NewValue
  10. End Property
  11.  
  12. Public Property Get PublicMyControlArray(ByVal Index As Long) As Control
  13.   PublicMyControlArray = myControlArray(Index)
  14. End Property
  15.  
  16. Public Property Set PublicMyControlArray(ByVal Index As Long, ByVal NewValue As Control)
  17.   Set myControlArray(Index) = NewValue
  18. End Property
  19.  
  20.  
  21.  
  22. Private Sub CommandButton1_Click()
  23.   Dim userPromtedValue(1 To 10) As String
  24.   For i = 0 To thisSubsetNo
  25.     Dim myControl As Control
  26.     Set myControl = userForm1.PublicMyControlArray(2 * i + 1)
  27.     userPromtedValue(i) = myControl.Text
  28.     PublicUserPromtedValue(i) = userPromtedValue(i)
  29.     MsgBox "text" & userPromtedValue(i)
  30.   Next i
  31.   Me.Hide
  32. End Sub
  33.  
  34. Private Sub CommandButton2_Click()
  35.   Me.Hide
  36. End Sub
  37.  
  38. Private Sub UserForm_Initialize()
  39.  
  40.   Dim newTextBox As TextBox
  41.   Dim myControl As Control
  42.   Dim myControl1 As Control
  43.   Dim myControl2 As Control
  44.   Dim myControl12 As Control
  45.   'Dim myControlArray() As Control
  46.  
  47.   Dim lngNextTop As Long
  48.   Dim lngTitleBarHeight As Long
  49.   Dim i As Long
  50.   ReDim myControlArray(2 * thisSubsetNo + 1)
  51.  
  52.   Const cTextBoxHeight As Long = 18
  53.   Const cTextBoxWidth As Long = 70
  54.   Const cGap As Long = 20
  55.  
  56.  
  57.   lngTitleBarHeight = Me.Height - Me.InsideHeight
  58.  
  59.   lngNextTop = cGap
  60.  
  61.   Load userForm1
  62.  
  63.   For i = 0 To thisSubsetNo
  64.     'Create label
  65.     Set myControl = Me.Controls.Add("Forms.Label.1", "myControl")
  66.  
  67.     With myControl
  68.       .Visible = True
  69.       .Width = cTextBoxWidth
  70.       .Height = cTextBoxHeight
  71.       .Left = cGap
  72.       .Top = lngNextTop
  73.       .Caption = thispath(i)
  74.     End With
  75.  
  76.     Set userForm1.PublicMyControlArray(2 * i) = myControl
  77.  
  78.     'Create textbox
  79.     Set myControl1 = Me.Controls.Add("Forms.TextBox.1", "myControl1")
  80.     With myControl1
  81.       .Visible = True
  82.       .Width = cTextBoxWidth + 50
  83.       .Height = cTextBoxHeight + 5
  84.       .Left = cGap + cTextBoxWidth + cGap
  85.       .Top = lngNextTop
  86.     End With
  87.  
  88.     Set userForm1.PublicMyControlArray(2 * i + 1) = myControl1
  89.  
  90.     'Calculate top position for next control
  91.     lngNextTop = lngNextTop + cTextBoxHeight + cGap
  92.  
  93.     'Resize form
  94.     Me.Height = lngNextTop + cTextBoxHeight + cGap
  95.  
  96.   Next i
  97.  
  98.   Set myControl = Nothing
  99.   Set myControl1 = Nothing
  100.  
  101. End Sub
Jun 28 '07 #1
0 1342

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Chris Styles | last post by:
Dear All, I've been using some code to verify form data quite happily, but i've recently changed the way my form is structured, and I can't get it to work now. Originally : The form is...
5
by: Jonathan Williams | last post by:
Hi, I have an object which inherits from WebControl (CUSTOM : WebControl) In this object I have code in which I add child contols: protected override void CreateChildControls() {...
9
by: Craig Andrews | last post by:
Hello, How do I access HTML CheckBoxs in the codebehind section of an ASP.NET program? In the old Interdev days one would: For i = 1 to Request.Form("CheckBox").Count if...
0
by: Jack | last post by:
Hi, We built a COM object(let say, rtblink.dll) and extend RTD function by implementing IRtdServer in our coding. The platform was .Net 1.1 , and IDE is Visual Studio7.0. We use regasm to pu the...
3
by: nagen | last post by:
I have an existing excel file & i would like to enter a specific value at cell A1 in Excel2003 using asp.net 2.0 how can i do this?
4
by: ally | last post by:
In VB 2K5 Express I've created a panel named "Configure" with eight label, and eight combobox controls; and finally a button named "Apply." The label controls are sequentially named Channel0...
6
by: Chuck Anderson | last post by:
My knowledge of JavaScript is limited. I learn from example and then adapt those examples to suit my needs. I have stumped myself on this one. I have a form with checkboxes that I want to...
3
by: judy.j.miller | last post by:
Does anyone know why i can't access a form element value using dot notation in firefox, when i'm in a function. Works ok in the body. I'm trying to do this: var FarTemp = faren.temp.value; I...
1
by: karthik25 | last post by:
Hi, The following code creates a tab container, 5 tab panels, buttons, labels. I was able to add event handlers to the button. But how am i supposed to access the dynamically created labels? For...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.