object required error 424?

[pjbur2005 via OfficeKB.com]

Not sure if i am getting this over very well but here goes

I have a list called "Datalables"

in this list i have a value ""AdmissionDate"

I have a range called "AdmissionDate"

I have a userform With all controls prefixed with "UF"

in the userform i use a variable called "UFAdmissionDate"

i loop through the "Datalabels" list if = "AdmissionDate" i want the range
"AdmissionDate" to be given the Value Of "UFAdmissionDate"

if value is not "AdmissionDate" then i get the value of the control called
"UF" & the list value.
M when i run this i get 424 error object required can someone see what i am
doing wrong

Public Sub datatransfer()
With Workbooks("Cram")

..Activate
With Worksheets("info")
'cram is userform
With Cram
For Each field In Range("datalabels")
form = "uf" & field
Select Case field
Case "AdmissionDate"
' following line produces object required error 424
Worksheets("info").Range(field) = form.
Value
Case Else
Range(field) = .Controls(form).Value
End Select
Next
End With

End With
End With
End Sub

 

[Jonathan West]

You've posted to a group that deals with VBA programming in Word. The Excel
experts hang out in the newsgroup microsoft.public.Excel.programming (which
is probably mapped to by one of the OfficeKB.com forums you are posting
from). In order to get answers from Excel experts, I suggest you find the
Excel programming group and re-post your question there.

 

[Helmut Weber]

Hi,

that is Excel-code.
Try:

microsoft.public.excel.programming

 

[Jay Freedman]

Not sure if i am getting this over very well but here goes

I have a list called "Datalables"

in this list i have a value ""AdmissionDate"

I have a range called "AdmissionDate"

I have a userform With all controls prefixed with "UF"

in the userform i use a variable called "UFAdmissionDate"

i loop through the "Datalabels" list if = "AdmissionDate" i want the range
"AdmissionDate" to be given the Value Of "UFAdmissionDate"

if value is not "AdmissionDate" then i get the value of the control called
"UF" & the list value.
M when i run this i get 424 error object required can someone see what i am
doing wrong

Public Sub datatransfer()
With Workbooks("Cram")

Activate
With Worksheets("info")
'cram is userform
With Cram
For Each field In Range("datalabels")
form = "uf" & field
Select Case field
Case "AdmissionDate"
' following line produces object required error 424
Worksheets("info").Range(field) = form.
Value
Case Else
Range(field) = .Controls(form).Value
End Select
Next
End With

End With
End With
End Sub

You've posted this Excel question in a Word forum. You're more likely to get
good help in http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200809/1
or the equivalent
http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.excel.programming.

My guess would be that the value of the string named 'form' doesn't match any of
the names of controls in the userform, so in the statement under Case Else the
value of .Controls(form) is Nothing. You can check this by adding
..Controls(form) to the Watch window and then using F8 to single-step through the
macro to the point where that statement is executed.

 

[pjbur2005 via OfficeKB.com]

Sorry all posted in wrong group thanks for letting me know

Not sure if i am getting this over very well but here goes

[quoted text clipped - 39 lines]

End With
End Sub

You've posted this Excel question in a Word forum. You're more likely to get
good help in http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200809/1
or the equivalent
http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.excel.programming.

My guess would be that the value of the string named 'form' doesn't match any of
the names of controls in the userform, so in the statement under Case Else the
value of .Controls(form) is Nothing. You can check this by adding
.Controls(form) to the Watch window and then using F8 to single-step through the
macro to the point where that statement is executed.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit.