Hi All

I have a system which tracks training courses and jobs for staff.

All the training courses are laid out in columns on one worksheet. The
jobs are columns on another sheet and the rows on both sheets are the
staff.

When a user adds a new job I need to know which course provides the
training to do that job. Therefore I need a nice way to ask the user
which column on the course sheet is for the right course as I link the
course with the job on the job sheet.

I could use a data validation with a list referring to a dynamic range
name based on the course headers but this is proving difficult due to
the formatting of the course headers which I cannot change.

Is it possible to use a control or something to get the functionality
of a "cell selector" like Excel provides when your are selecting a
cell or range in, say, Conditional Formatting?

Then I could read the cell that the user selects and deduce the
correct training course column.

Is such a control available?

Chrisso

I am thinking a UserForm with option buttons, checkboxes or radio dials
(whatever) for the user to click with a pre-defined course attached via the
Click event to the category descriptions on the UserForm. If that sentence
is too long then'
1. Enumerate the categories of training on a UserForm
2. When the user enters a new job, the UserForm pops up
3. The user selects the appropriate category for the job by clicking
4. The click event code then captures the job entry and adds it to the
appropriate column.

"Chrisso" wrote:

> Hi All
>
> I have a system which tracks training courses and jobs for staff.
>
> All the training courses are laid out in columns on one worksheet. The
> jobs are columns on another sheet and the rows on both sheets are the
> staff.
>
> When a user adds a new job I need to know which course provides the
> training to do that job. Therefore I need a nice way to ask the user
> which column on the course sheet is for the right course as I link the
> course with the job on the job sheet.
>
> I could use a data validation with a list referring to a dynamic range
> name based on the course headers but this is proving difficult due to
> the formatting of the course headers which I cannot change.
>
> Is it possible to use a control or something to get the functionality
> of a "cell selector" like Excel provides when your are selecting a
> cell or range in, say, Conditional Formatting?
>
> Then I could read the cell that the user selects and deduce the
> correct training course column.
>
> Is such a control available?
>
> Chrisso
>
>

Dim r as Range
on Error Resume Next
set r = Application.InputBox("Select Course with mouse:",type:=8)
On error goto 0
if r is nothing then
msgbox "No course selected"
exit sub
End if

--
Regards,
Tom Ogilvy


"Chrisso" wrote:

> Hi All
>
> I have a system which tracks training courses and jobs for staff.
>
> All the training courses are laid out in columns on one worksheet. The
> jobs are columns on another sheet and the rows on both sheets are the
> staff.
>
> When a user adds a new job I need to know which course provides the
> training to do that job. Therefore I need a nice way to ask the user
> which column on the course sheet is for the right course as I link the
> course with the job on the job sheet.
>
> I could use a data validation with a list referring to a dynamic range
> name based on the course headers but this is proving difficult due to
> the formatting of the course headers which I cannot change.
>
> Is it possible to use a control or something to get the functionality
> of a "cell selector" like Excel provides when your are selecting a
> cell or range in, say, Conditional Formatting?
>
> Then I could read the cell that the user selects and deduce the
> correct training course column.
>
> Is such a control available?
>
> Chrisso
>
>