Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Control Box Properties | Excel Discussion (Misc queries) | |||
How to control chart properties from worksheet | Excel Discussion (Misc queries) | |||
change control properties according to a cell value | Excel Worksheet Functions | |||
How do I change activex control properties from a macro | Excel Discussion (Misc queries) | |||
Spinner Control Properties | Excel Worksheet Functions |