Change Control properties in VBE
 

Hi Chip

The very man, thanks for responding.
Unfortunately I didn't explain myself clearly and
apologies. As I understand it this code changes
properties whilst the (userform) code is running. On
termination the original properties are restored(?).

What I really want to do is "code" edit the properties
with details from a spreadsheet table, so that they remain
that way when the project is saved. A lazy way of editing
each manually.

With regards,
Sandy


-----Original Message-----
Sandy,

You code is close. Controls are indexed starting at 0, so
you can
use code like the following:

Sub AAA()
Dim i As Integer
With UserForm1.Controls
For i = 0 To 4
.Item(i).Height = Cells(i + 1, 1).Value
Next i
End With
UserForm1.Show
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Sandy V" wrote in message
...
Hi

I would like to know how to programmatically change
Userform Control properties in the VBE, with data in
cells. Eg do something similar to this:

With Userform1.Controls
For i = 1 to 5
.Item(i).Height = Cells(i,1).value
Next i
End With

www.cpearson.com/excel/vbe.htm
Chip Pearson has excellent examples of how to add/delete
code & modules, but unless I've missed something I can't
see how to amend existing Control properties.

Thanks in advance,
Sandy



.

Change Control properties in VBE
 

Sandy,

Use the Designer object to access the form in design mode. E.g.,

Dim N As Integer
For N = 0 To 4

ThisWorkbook.VBProject.VBComponents("UserForm1").D esigner.Controls
(N).Height = _
Cells(N + 1, 1)
Next N


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"Sandy V" wrote in message
...
Hi Chip

The very man, thanks for responding.
Unfortunately I didn't explain myself clearly and
apologies. As I understand it this code changes
properties whilst the (userform) code is running. On
termination the original properties are restored(?).

What I really want to do is "code" edit the properties
with details from a spreadsheet table, so that they remain
that way when the project is saved. A lazy way of editing
each manually.

With regards,
Sandy


-----Original Message-----
Sandy,

You code is close. Controls are indexed starting at 0, so
you can
use code like the following:

Sub AAA()
Dim i As Integer
With UserForm1.Controls
For i = 0 To 4
.Item(i).Height = Cells(i + 1, 1).Value
Next i
End With
UserForm1.Show
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Sandy V" wrote in message
...
Hi

I would like to know how to programmatically change
Userform Control properties in the VBE, with data in
cells. Eg do something similar to this:

With Userform1.Controls
For i = 1 to 5
.Item(i).Height = Cells(i,1).value
Next i
End With

www.cpearson.com/excel/vbe.htm
Chip Pearson has excellent examples of how to add/delete
code & modules, but unless I've missed something I can't
see how to amend existing Control properties.

Thanks in advance,
Sandy



.

Change Control properties in VBE
 

Hi Chip,

That's exactly what I want. Works a treat and will save
me a lot of time.

Thanks very much,
Sandy

-----Original Message-----
Sandy,

Use the Designer object to access the form in design
mode. E.g.,

Dim N As Integer
For N = 0 To 4

ThisWorkbook.VBProject.VBComponents
("UserForm1").Designer.Controls
(N).Height = _
Cells(N + 1, 1)
Next N


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"Sandy V" wrote in message
...
Hi Chip

The very man, thanks for responding.
Unfortunately I didn't explain myself clearly and
apologies. As I understand it this code changes
properties whilst the (userform) code is running. On
termination the original properties are restored(?).

What I really want to do is "code" edit the properties
with details from a spreadsheet table, so that they
remain
that way when the project is saved. A lazy way of
editing
each manually.

With regards,
Sandy


-----Original Message-----
Sandy,

You code is close. Controls are indexed starting at 0,
so
you can
use code like the following:

Sub AAA()
Dim i As Integer
With UserForm1.Controls
For i = 0 To 4
.Item(i).Height = Cells(i + 1, 1).Value
Next i
End With
UserForm1.Show
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Sandy V" wrote in message
...
Hi

I would like to know how to programmatically change
Userform Control properties in the VBE, with data in
cells. Eg do something similar to this:

With Userform1.Controls
For i = 1 to 5
.Item(i).Height = Cells(i,1).value
Next i
End With

www.cpearson.com/excel/vbe.htm
Chip Pearson has excellent examples of how to
add/delete
code & modules, but unless I've missed something I
can't
see how to amend existing Control properties.

Thanks in advance,
Sandy



.



.