: A newbie  question.

Is the question  a newbie or the questioner? :)


: Can someone please tell me how to program  in vba  for Excel
: a message  or list  box to appear whenever a cell  in certain
: column  is selected?

Where do you wish the message or list box  to appear?
On a form, on the same cell, in a different cell, etc. What
is in the message or list box? How is it filled with
information?

I am the newbie  and trying to get to grips with VBA.
I have a table in my Excel Spreadsheet consisting of two columns by 7 rows.
The one column  consists of Code Numbers and the other a corresponding
description.

I have to enter the codes in a column in another section of the spreadsheet
I would like the table to appear when I select a cell  to enter a code in this
column so that I can see its description without having to scroll back and forth
to see the table.

You might want to use a simple Vlookup function or even simpler to use
the freeze panes option under Window in the menubar.

: I have a table in my Excel Spreadsheet consisting of two columns
: by 7 rows. The one column  consists of Code Numbers and the other
: a corresponding description.

How long are the descriptions? Can you provide an example?
Are these codes dynamic or static? (Do they change or always
remain the same?) Have you named the range where the table is?
Will there be different tables for other columns?


: I have to enter the codes in a column in another section of the
: spreadsheet I would like the table to appear when I select a
: cell  to enter a code in this column so that I can see its
: description without having to scroll back and forth to see the
: table.

Are you trying to learn the codes or can the VBA program
insert them for you? If you are no trying to learn them then
perhaps you could create a form with each description as an
option. Clicking on that option would then insert the code
into the cell.

The VBA program  can insert them
How do I create the form with each description as an option so that clicking
on the option can then insert the code in into the cell  and the description in
the adjacent column  alongside the code?
.

Not sure what happened but your post and my reply but both were deleted.
I will Try again.

An example of my Table is as follows:
Item Code Description XA9301 Alpha numeric Chars Max
of 20 XA9302 Childrens shoes XA9303 Childrens Pants XA9305 Baby
Growers XA9306 Baby Feeders XA9307 Dolls Clothes XA9308 Plastic Cups
XA9327 Plastic Saucers XA9328 Swimwear

The table is static and does not change.
The Tables Range has not been named.
There is only one table.

: An example of my Table is as follows:
: Item Code Description XA9301 Alpha numeric
: Chars Max of 20 XA9302 Childrens shoes XA9303 Childrens
: Pants XA9305 Baby Growers XA9306 Baby Feeders XA9307
: Dolls Clothes XA9308 Plastic Cups XA9327 Plastic Saucers
: XA9328 Swimwear

Erm. All in one cell! Would it have killed you to take the
time to place that in a tab delimited format for this email so I
could have copied it into a spread sheet easier?

Item Code Description
XA9301 Alpha numeric Chars Max of 20
XA9302 Childrens shoes
XA9303 Childrens Pants
XA9305 Baby Growers
XA9306 Baby Feeders
XA9307 Dolls Clothes
XA9308 Plastic Cups
XA9327 Plastic Saucers
XA9328 Swimwear



Go to the VBA Project and add this to the sheet code. (This
code may wrap in your window.) Change the 4 to whichever column
you want to watch (4 is column  D).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 4 And Target.Columns.Count = 1 And Target.Rows.Count
= 1 Then
Load frmItemCodes
frmItemCodes.Show
End If

End Sub

Create a form named frmItemCodes. On that form add a list  box
named lboItemCodes. Set the following:

BoundColumn = 1
ColumnCount = 2
ColumnHeads = true
RowSource = a2:b10 (Assumes table with headings is at a1:b10)

Double-click somewhere on the form and add this code.

Private Sub lboItemCodes_Click()
ActiveCell.Value = lboItemCodes.Value
Unload frmItemCodes
End Sub

When you click on a single cell  in the watched column, the
SelectionChange event loads the form. Clicking on a selection
unloads the form. I found the form block my view of the cell. You
can position the form by changing its Position properties.


: The table is static and does not change.
: The Tables Range has not been named.
: There is only one table.

For readability, place your answers below each question. It
will also make it easier for you not to miss a question  and easier
for other people reading the message.

Sorry for my Table in the way it came out in the post. I actually copied and
pasted the table from my spreadsheet and assumed it would stay in the tabulated
form.

Thanks for the information. I will study your answer and try it out.

I now have my spreadsheet working with your help which is greatly appreciated.
Just one question. I did not include your following line of code:

If Target.Column = 4 And Target.Columns. Count = 1 And Target.Rows. Count
= 1 Then

Please explain why the above line is required as it seems to work with and
without it.

: I now have my spreadsheet working with your help which is
: greatly appreciated.

You are welcome.

: Just one question. I did not include your following line of
: code:

It restricts the range to a single cell  in the "D" column
which you can click on and get the form.


: If Target.Column = 4

Fourth column  ("D").


: And Target.Columns.Count = 1

One column wide.


: And Target.Rows.Count = 1

One Row tall.

Try selecting a group of cells without this and with it to see
the difference. I assumed you only wanted the form to pop up when
you selected  a single cell in the fourth column.

I could write it this way as well.

If Target.Column = 4 And Target.Cells.Count = 1 Then

If you start getting "out of memory errors" you'll need to
move the Load and Unload statements to the Worksheet_Activate and
Worksheet_Deactivate (or the workbook Open and BeforeClose) sub
routines and then hide and show the form as appropriate.

Private Sub Worksheet_Activate()
Load frmItemCodes
End Sub

Private Sub Worksheet_Deactivate()
Unload frmItemCodes
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Ignore multi-cell selections.
If Target.Column = 4 And Target.Cells.Count = 1 Then
frmItemCodes.Show
End If
End Sub

Private Sub lboItemCodes_Click()
ActiveCell.Value = lboItemCodes.Value
frmItemCodes.Hide
End Sub

We made a comparison of this msg box  method with data validation.

Data validation needs three clicks, and cannot see descriptions.

Msg box method needs two clicks and see the whole table, but it
involves a lot more in setting up.

It appears we need not LOAD frmItemCodes.
Have we missed anything please?