userform variables

[pjbur2005 via OfficeKB.com]

Hi there hope some one can point me in the right direction. i dont seem to
be able to see the wood for the trees.

I have a userform that I use to collect input from users, i also populate it
from a data source. i use a list of field names that relate to both the range
names on a worksheet and with a prefix of "UF" to the userform both in terms
of controls and variables. I loop through the list and with the controls i
use Range(field) = .Controls(form).Value. how do i deal with the variables
"AdmissionDate", "DateTimeStamp" which in the code for the userform are
prefixed by "UF"
ie how do i pass the values to the relevant range

please see following code

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", "DateTimeStamp"

'What do i use here?

Case Else
Range(field) = .Controls(form).Value
End Select
Next
End With

End With
End With
End Sub

many thanks in anticipation

 

[JLGWhiz]

If you are trying to pass the variable "form" to a range on the worksheet,
then assume the range is cell A1:

ActiveSheet.Range("A1") = form 'substitute actual sheet and range

This would execute when the case is "Admission Date" or "Time Stamp"

 

[pjbur2005 via OfficeKB.com]

thanks for your input,

form contains the variable name "UFAdmissiondate" i want (say) range "A1" to
contain the value of "UFAdmissionDate"

If you are trying to pass the variable "form" to a range on the worksheet,
then assume the range is cell A1:

ActiveSheet.Range("A1") = form 'substitute actual sheet and range

This would execute when the case is "Admission Date" or "Time Stamp"

Hi there hope some one can point me in the right direction. i dont seem to
be able to see the wood for the trees.

[quoted text clipped - 34 lines]

many thanks in anticipation

 

[JLGWhiz]

You probably want to change :
From: form = "UF" & field
To: form = "UF" & field.Value

You should also Dim field As Range at the beginning of your macro.
Then, the syntax for the code that I gave you should work.

Let's say that the worksheet cell C5 is in Range("Data Labels") and is named
Admission Date with a value of "357". When the For...Next loop hits that
cell,
it triggers the Case and the value of "form" is now = UF357. You can make
any cell on any sheet equal form and UF357 will appear in that cell.

I am not sure that Admission Date is a cell name or range name but I assume
that it is one or the other by the way it is used. I do not see it in the
variables declarations because there were none posted.

thanks for your input,

form contains the variable name "UFAdmissiondate" i want (say) range "A1" to
contain the value of "UFAdmissionDate"

If you are trying to pass the variable "form" to a range on the worksheet,
then assume the range is cell A1:

ActiveSheet.Range("A1") = form 'substitute actual sheet and range

This would execute when the case is "Admission Date" or "Time Stamp"

Hi there hope some one can point me in the right direction. i dont seem to
be able to see the wood for the trees.

[quoted text clipped - 34 lines]

many thanks in anticipation

 

[pjbur2005 via OfficeKB.com]

Tried that and got error 424 object required........

Admissiondata is a value of a list called datalables
it is also a range callled admissiondate
it is also a variable within the code of userform "cram" when "UF" is a
prefifix (ie UFADmissionDate)
UFadmissionDate will contain a date derived on the userform using thre test
boxes for dd, mm and yy
these are put together for the date.

UFAdmissionDate is publically delacred as a date globally

You probably want to change :
From: form = "UF" & field
To: form = "UF" & field.Value

You should also Dim field As Range at the beginning of your macro.
Then, the syntax for the code that I gave you should work.

Let's say that the worksheet cell C5 is in Range("Data Labels") and is named
Admission Date with a value of "357". When the For...Next loop hits that
cell,
it triggers the Case and the value of "form" is now = UF357. You can make
any cell on any sheet equal form and UF357 will appear in that cell.

I am not sure that Admission Date is a cell name or range name but I assume
that it is one or the other by the way it is used. I do not see it in the
variables declarations because there were none posted.

thanks for your input,

[quoted text clipped - 13 lines]