Using a Form to control variable fields.

[Memphus01]

I have created a form that allows me to enter names based on a source data
field.

Form uses combo box to allow me to ensure I do not mispell names (i have
vlookups tied to those names) and it allows me to enter a current rate (also
combo box with rowsource) and also a line for position.

I would like to be able to have that form control a value in a field, but
the field is variable. For example:

A B C

1 Position Name Rate
2 Cook
3 Cook
4 Front End
5 Front End
6 Front End
7 Front End
8 Front End
9 Clean Up

I would like for the form to enter the name I looked up and the salary I
input into the next available B,C column for the Position I enter for the
current page.

Is there a way to create a variable control source?

 

[Jacob Skaria]

Hi

Get the next row using the below code

intRowB = ActiveSheet.Range("B65536").End(xlUp).Row
intRowC = ActiveSheet.Range("C65536").End(xlUp).Row

If this post helps click Yes

 

[Memphus01]

Jacob- Thanks for the quick reply - I would like it to find the first blank
fields in B and C that match the position entered in the form---

 

[Howard31]

Do you mean you wantto be able to update Nmae and Rate based on the selection
of Position, with the corresponding values in the spreadsheet? If this is the
case why not use a Vlookup as follows:

TextBoxName.Text =
Application.Vlookup(ComboBoxPosition.Text,Range(Cells(1,1),Cells(50,3),2,False)

This should give you the Name of the corresponding Position

 

[Memphus01]

thanks howard- I may have poorly communicated my intent -- the position can
vary (the vlookup is for performance metrics from another sheet)

I need it to fill B and C based on the value I input in the form (that
should match a position in column A)

 

[Howard31]

If I understood correctly you want to enter values in Column A and B in the
row that matches the selection in the Combo for the oositions, in that case I
think there is 2 solutions, 1. Use the Find Method of the Range Object to
find the cell that matches your selected value from the combo (The Find
Method returns a Range object so you can use the range and offset it to
column A and B), 2. find the the cell in column A by using the ListIndex
property of the Combo, this will only work if the combo is populated by
Cilumn A, and you might need to ajust the return value of ListIndex by -1 or
more.

 

[Jacob Skaria]

Try this...

Dim intRowA
intRowA = 1
Do while Range("A" & intRowA) <> strName
intRowA = intRowA + 1
Loop

Range("B" & intRowA) = value
Range("C & intRowA) = value


f this post helps click Yes

 

[Memphus01]

I am doing a terrible job of explaining this... thanks for your patience.

in userform1--i have three combo boxes:
Name
Rate
Pos

I would like Name and Rate to control columns B and C of current page where
column A matches Pos as entered on form and where B and C are blank.

does that make sense?

 

[Jacob Skaria]

Have you tried this

Dim intRowA
intRowA = 1
Do while Range("A" & intRowA) <> strName
intRowA = intRowA + 1
Loop

Range("B" & intRowA) = value
Range("C & intRowA) = value


f this post helps click Yes