Frame Identity on VBA Excel Forms

[peterpcu]

Hi There,

I have been using the code below to add a label to a frame within a form with its attributes determined by cell contents on a worksheet. The code works very well but I would like to use alertantive frame names as opposed to Fra1 such as Fra2, Fra3 or whatever.

When I try replacing the:-
Set lbl1 = Fra1.Controls.Add("Forms.label.1")
with Set lbl1 = "Fra"&inc&".controls.add("forms.label.1")"

where inc=1

the form does not pick up the label - I also tried replacing the quotation marks with chr(34) but that did not seem to help.

any ideas??

Regards

Pete

Set lbl1 = Fra1.Controls.Add("Forms.label.1")
             With lbl1
                 .Caption = Sheets("inputs").Cells(3, 5)
                 .Left = Sheets("inputs").Cells(3, 3)
                 .Top = Sheets("inputs").Cells(3, 2)
                 .Width = Sheets("inputs").Cells(inc, 6)
                 .Height = Sheets("inputs").Cells(inc, 7)
                 .Font.Bold = True
                 .Font.Size = 10
                 .ForeColor = RGB(0, 0, 160)
                 .Name = "lbl1"
                 End With

 

[SkipVought]

Hi,,

Try

Set X = Controls("Fra" & inc).COntrols.Add......

Skip,
[sub]
[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE[/sub]

 

[peterpcu]

Thanks Skip,

I tried your suggestion but a compile error came up and highlighted the word Controls before the ("Fra" part....... message said 'sub or function not defined'

Pete

 

[SkipVought]

Try quallifying COntrols with the FORM reference.

How to use the Watch Window as a Power Programming Tool faq707-4594




Skip,
[sub]
[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE[/sub]

 

[peterpcu]

OK,

Now it runs without a compile error which is good but is not displaying the labels on the form.

My code is now:-

 Set X = form.Controls("Fra" & Cells(inc, 3)).Controls.Add("forms.label.1")
          With X
                 .Caption = Sheets("inputs").Cells(inc, 6)
                 .Left = Sheets("inputs").Cells(inc, 4)

where cells(inc,3)= 1

 

[SkipVought]

Set X = form.Controls("Fra" & [b]Sheets("inputs").[/b]Cells(inc, 3)).Controls.Add("forms.label.1")
          With X
                 .Caption = Sheets("inputs").Cells(inc, 6)
                 .Left = Sheets("inputs").Cells(inc, 4)

Skip,
[sub]
[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE[/sub]

 

[peterpcu]

Hi Skip,

Tried that correction - but still no show of the labels on the form.

Regards

Pete

 

[SkipVought]

Please post your code.


Skip,
[sub]
[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE[/sub]

 

[peterpcu]

Hi Skip,

Sorry I did not respond to you offer - I have been out of the country.

I have now solved my problem by using the .name= property route when defining the frame. Many thanks for your help

Regards

Pete