set .controlsource in UserForm_Initialize

izyrider · #1 (**permalink**) 12-30-06, 13:22

i have a UserForm with

_Activate()
Me.boxLastName = Worksheets("dPeople").Cells(Me.boxRow, cstLastName)

_Terminate()
Worksheets("dPeople").Cells(Me.boxRow, cstLastName) = Me.boxLastName

everything is working fine, but i am unhappy manually-feeding and later manually-harvesting 50 controls.

all my attempts at setting
Me.boxLastName.ControlSource = Xxxxxxxxxxxxxx
at _Activate or _Initialize() have failed!

any hints are very welcome.

izy

LATER: can't edit the topic but it should have read
set .ControlSource in UserForm_Initialize or _Activate
...and i have tried literals in place of Me.boxRow in both events - zilch!

izyrider · #2 (**permalink**) 12-30-06, 13:54

Me.boxFirstName.ControlSource = Worksheets("dPeople").Cells(Me.boxRow, cstFirstName).Address

in _Activate has fixed the error messages, but a known-populated cell comes up empty

hmmmm

izy

izyrider · #3 (**permalink**) 12-30-06, 13:59

réglé!

Me.boxFirstName.ControlSource = Worksheets("dPeople").Cells(Me.boxRow, 7).Address(, , xlA1, True)

and it works!
.Address makes a lot of sense, but the (, , xlA1, True) is currently a magic mystery.

izy

norie · #4 (**permalink**) 01-01-07, 15:38

I'm not 100% sure what you are asking but you can loop through a forms controls collection like this.

Code:

For Each ctl In Me.Controls
     Msgbox "Control " & ctl.Name & " is a" & TypeName(ctl)
Next ctl

Internet Services

Web Development

Digital Marketing

Consumer Tech