data val box with more than 1 column data

[MUSASHI]

first off, I apologize if I missed another post on this.

What I am doing is a timesheet. basically 2 rows of data for each employee,
on row 1 an employee number, on row 2 the Employee name.

btw, those 2 rows also serve as ST and OT hours by cost code summed up in another column as daily hours.

I like the data val list boxes because they arent visible until the cell is selected. We have to deal with so many employees that autofilling the name makes the process much much faster...so if I could click on row 1 select an emplyee #, the row 2 name box is filled.

If I could hit a cell, see data val list with the number..great as long as I can remember everyones number....with couple hunfdred employees...heheh aint gonna happen so if I could see the corresponding name as reference material for the selection great!!!

OR

a way to make combobox invisible unless cell is selected?

Thanks in advance!!!

[mdmackillop]

Hi Musashi,

Is the attached any use?

MD

[Zack Barresse]

Hello,

One option (and I haven't looked at MD's example yet) would be to input everybody's name and their code 1 time on a seperate sheet, labeled something creative like 'info' or something. Then in the cell next to your Data Validation (List) you can use a VLOOKUP formula. Maybe something like ...

=IF(A1<>"",VLOOKUP(A1,info!$A$1:$B$100,2,0),"")

... which looks at cell A1 (assuming this is your validated cell), finds the value in column A of sheet 'info' and returns the corresponding value from the second column in your index table (A:B); the zero at the end signifies that it must be an exact match returned.

Is that what you're looking for?

[MUSASHI]

great as long as I can remember everyones number....with couple hunfdred employees...heheh aint gonna happen

heya Fire, in fact I have a couple named ranges on a seperate sheet, one is "ID_num" and one is "emp_name"

problem is, if I select the data val box in row 1, I see a list of 200 numbers.....unless I have them memorized.....I dont know the corresponding "emp_name"

I got a pretty good handle on doing vlooks, and use them a lot. As I learn more, the more I want, heheh if you know what I mean.

I just looked at the attached userform, and that is the basic idea, let me spelunk on that for a while.

Thanks a bunch!!!!!!! (btw, this is just the beginning, brace for additional!!!!! hehe)

[MUSASHI]

well, the userform deal will work, however, I need to modify the worksheet somewhat as well as filling the vlookup references with real employee data. I need like 20 potential folks.....no biggie, however, I need to insert about 10 rowns on top of all the code, this will be where date, job#, cost codes etc will go.


I see the code referencing a column.....irregardless of what row...can I get like 10-12 rowns on top with nothing in it, or references to it? If so how?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column() = 1 And Target.Row() Mod 2 = 0 Then
UserForm1.Show
End If
End Sub

thx!!
opps, almost forgot, thx for the form MD!

[mdmackillop]

Hi Musashi.
The following change will omit rows 1 to 12 from triggering the macro.
MD

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row() > 12 Then
        If Target.Column() = 1 And Target.Row() Mod 2 = 0 Then
            UserForm1.Show
        End If
    End If
End Sub

[MUSASHI]

ok tried it, no go so far, heres what I did, basically moved the fields down to where the selection row was starting at 12, with the target row being 13..., then tried it at 13, 14.

got a compile error, Block If End If

what ya think?

[mdmackillop]

Don't understand the error, but here's another copy for rows 14 - 42. I've changed the userform operation slightly, which works a little better I think

[MUSASHI]

MD, works great, thx.


What I did was somehow excluded an end if, only had one of them in there. Sorry!

Thanks for your help.

Its 92 here today. Maybe thats it.

[mdmackillop]

42 here, maybe your processor is overheating!!!

[johnske]

Hi MUSASHI,

It seems MD has sorted your problem, this any help too?

[MUSASHI]

not exactly, but it will be someday! thanks!