MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

New to VBA -Can pick list function to also fill in next column cell?

  Asked By: Tracey    Date: Dec 07    Category: MS Office    Views: 1275

I'm looking into developing an Excel worksheet that uses VBA
linked to cells used in the Excel "pick from list" function.

I have the "Pick from list" working and now would like to develop
some VBA code to compare the input selected and drop a predefined
number in the cell to the left of the pick.

To allow future duplication of the linked inputs to easily populate
the template. I imagine assigning a variable lets say 1-5 to the
corresponding text in the column that the pick list is referenced
to. These columns being on a hidden worksheet. Maybe the pick inputs
with the various amounts right beside, in the next column.

When the corresponding text/variable is put in the cell a IF
statement(?) drops the preformated amounts related to the variable
1=$1000 2=$2500 etc.
If the IF linked to hidden worksheet cells the values could be
changed easily by other non VBA users of the template.

Also need to consider one of the pick list inputs =blank to allow
custom user input then the IF would allow valve to me manually put
into ajoining cell without crashing the macro.

I mighh be able to just record the pick from list development as a
macro and then step into and link the ajoining cell to the pick?



1 Answer Found

Answer #1    Answered By: Mehr Malik     Answered On: Dec 07

Sometimes, a good way to start is to record  actions as a macro  and then modify
from there, as you have suggested.

But note that the code  recorded will not necessarily by very good VBA. For a
start, it is recording the actions of keystrokes, which is not necessarily the
best way to accomplish a task. Also, it's going to have exact cell  references
in it, plus possibly the setting of lots of attributes that you don't want to
actually change. Use it for information, by all means, but remember that it is
definitely not "good" code.

If you want to handle adjacent cells, there are a couple of facilities you need
to use. cells  (row, col) is how you get to a cell when you already know its row
and column  numbers. It's easy to calculate these when you know the row and
column of your parent cell (Row and Column properties). Alternatively, there is
an Offset (row, column) method that will return a reference to a cell relative
to another. And, of course, the Range method will accept a normal cell
reference (e.g. "C3").