Dropdown List Add New Feature Needed

[Mycelium]

Hi folks.

How can I create a button or list entry that takes the user to a cell to
add a "new item" or "new user" to a dropdown list array by way of row
insertion and cursor placement... that takes the last entry in the
existing drop down list to use as an "add new" "button" or trigger?

The event is to add a new row to an existing named array, which
automatically updates all code in the workbook, if the row is inserted
from within the existing array.

So the last entry in the drop down list array could (would) be an "Add
New" selection for the user to choose. When the fill is performed, the
action could be to test that cell content and act accordingly. The user
would get switched to the array with the cursor in the left most cell of
the newly inserted row. This also has the effect of keeping the cell
formatting as well for solid color arrays, that is.

Is this possible? I suppose a test loop that tests the cell content,or
one that tests for and grabs the content even before it makes it to a
cell. Anyway, when one selects the add new selection from the drop down
list, instead of filling that data into the cell, it performs the insert
and pop over function so that a new record can be added.

I have a sheet that has about eight small,named arrays on it and use it
as a master data sheet for a 'presentation' type sheet that draws much of
its data from lookups to the array sheet.

A simple row insertion on that sheet updates all arrays, etc. in the
entire workbook. Nice job, guys. I LIKE IT!

So, anyway, is this a good approach for expanding the contents of an
array? Is keeping all these arrays on a single sheet a good approach?
This is like yet another new form of a database configuration, in the
final analysis. Thanks for any thoughts or views.

At least one inquiring mind wants to know.

 

[Mycelium]

How can I create a button or list entry that takes the user to a cell to
add a "new item" or "new user" to a dropdown list array by way of row
insertion and cursor placement... that takes the last entry in the
existing drop down list to use as an "add new" "button" or trigger?

Well, guys?

Is it even possible?

 

[Mycelium]

Well, guys?

Is it even possible?

Well? Any help?

 

[Kai]

This group has become oblivious.

I guess any complicated question gets ignored.

 

[Patrick Molloy]

this news group ALWAYS tries to help. Mails like this one of yours is very
unhelpful

1) in you post, make sure that the SUBJECT is related to the question
2) phrase the question clearly and concisely
3) include any VBA that you have written.
4) if you have error messages, tell us what they are and where in th ecode
it breaks - give us the variable values too if possible

 

[Mycelium]

It was a simple question. A simple yes or no answer. It DOES DESCRIBE
EXACTLY what is desired in the title of the post.


You conveniently did NOT answer said post, merely only making yet
another peanut gallery comment having absolutely nothing to do with what
was asked for... AGAIN.

Try reading the original post. Maybe even try answering it, since that
is what you claim to be here for.

 

[Patrick Molloy]

as far as I can see, your original post, at 03:07 on 02/07/2009
has one word in the body.
That word is "WHY".
The subject "Dropdown List Add New Feature Needed" doesn't describe the
problem
So from my perspective, and please forgive me if I'm wrong, I can't see what
the issue actually is.

If the request is for some additional features to be added to a control ...
then that should be directed to Microsoft's Excel development team.

If the request is for help with a problem that you have with Excel, then
maybe if you could describe your requirement in a little more detail that
would be helpful.

 

[Patrick Molloy]

paste this code to a new module. The test procedure passes a range object to
the AddNewLine procedure

Option Explicit
Sub TEST()
AddNewLine Worksheets("Sheet1").Range("MyData")
ActivateRange Worksheets("Sheet1").Range("MyData")
End Sub

Sub AddNewLine(target As Range)
Dim bAvailable As Boolean
Dim cell As Range
Dim rName As String
bAvailable = True
'check space below is not used
With target
For Each cell In .Offset(.Rows.Count).Resize(2).Cells
If Not IsEmpty(cell) Then
bAvailable = False
Exit For
End If
Next
If bAvailable Then
.Rows(.Rows.Count).Copy
rName = .Name.Name
With .Resize(.Rows.Count + 1)
'.Rows(.Rows.Count).Copy
.Rows(.Rows.Count).PasteSpecial xlADORecordset
.Name = rName
.Rows(.Rows.Count).ClearContents ' omit if you need
formula
End With
Else
MsgBox "No room below table to textend it"
End If
End With
End Sub

Sub ActivateRange(target As Range)
With target
.Parent.Activate
.Cells(.Rows.Count, 1).Select
End With
End Sub


you can replicate this several ways.
one would be to have a cell on a sheet with a button next to it. the button
would have a macro assigned to it like:
sub ButtonClick()
AddNewLine Range( Range("A1") )
End sub

where cell A1 holds the name of the range you want to add to,

This isn't complete - but if its the right direction, then we're getting
somewhere.




_____________________

Hi folks.

How can I create a button or list entry that takes the user to a cell to
add a "new item" or "new user" to a dropdown list array by way of row
insertion and cursor placement... that takes the last entry in the
existing drop down list to use as an "add new" "button" or trigger?


The event is to add a new row to an existing named array, which
automatically updates all code in the workbook, if the row is inserted
from within the existing array.


So the last entry in the drop down list array could (would) be an "Add
New" selection for the user to choose. When the fill is performed, the
action could be to test that cell content and act accordingly. The user
would get switched to the array with the cursor in the left most cell of
the newly inserted row. This also has the effect of keeping the cell
formatting as well for solid color arrays, that is.


Is this possible? I suppose a test loop that tests the cell content,or
one that tests for and grabs the content even before it makes it to a
cell. Anyway, when one selects the add new selection from the drop down
list, instead of filling that data into the cell, it performs the insert
and pop over function so that a new record can be added.


I have a sheet that has about eight small,named arrays on it and use it
as a master data sheet for a 'presentation' type sheet that draws much of
its data from lookups to the array sheet.


A simple row insertion on that sheet updates all arrays, etc. in the
entire workbook. Nice job, guys. I LIKE IT!


So, anyway, is this a good approach for expanding the contents of an
array? Is keeping all these arrays on a single sheet a good approach?
This is like yet another new form of a database configuration, in the
final analysis. Thanks for any thoughts or views.


At least one inquiring mind wants to know.

 

[Mycelium]

as far as I can see, your original post, at 03:07 on 02/07/2009
has one word in the body.
That word is "WHY".

I am talking about the first post in the THREAD, not the follow ups by
me and others to pop the thread back up in your obviously flooded radar
screens.

The subject "Dropdown List Add New Feature Needed" doesn't describe the
problem

It does if you read the original post made to this thread. Which I
have mentioned doing before.

So from my perspective, and please forgive me if I'm wrong, I can't see what
the issue actually is.

The "Why" post was followed by others. Had you strung all those
together, you would have seen the sentence that asks you guys why you
ignore posts. It was done so that it would get noticed. It did. The
part you didn't notice was the reason a block of posts were made to begin
with.

If the request is for some additional features to be added to a control ...
then that should be directed to Microsoft's Excel development team.

The "request" is to determine the feasibility of the idea, if it is not
already being done somewhere.

If the request is for help with a problem that you have with Excel, then
maybe if you could describe your requirement in a little more detail that
would be helpful.

If you do not want to re-read the original post, I can re-iterate it for
you. It simply asks if a certain method is possible. There wasn't
anything to decide about who to ask or anything like that.

 

[Mycelium]

Thank you. I will try this out.

Ideally, I wanted the last line in a drop down list (from the array I
suppose) to be "add new" or the like, and selecting it would pop the user
over to the array at a new, blank line entry just above the "Add New"
line.

Heck, this should be a feature you guy incorporate into your engine so
that a user with a defined drop down list array can easily add a new line
by clicking the last line in the drop down, which would be tagged as "Add
New".

This should already be part of the MS drop down code/function engine.
Sad that it is not.

Get those programmers to coding there!

Thanks again.

 

[Gord Dibben]

There are no MS "you guys" hanging around here.

Just a bunch of users like yourself who volunteer their time and expertise
to try to assist other users.

We have no engines in which to incorporate your requests.


Gord Dibben MS Excel MVP

 

[Mycelium]

There are no MS "you guys" hanging around here.

Just a bunch of users like yourself who volunteer their time and expertise
to try to assist other users.

We have no engines in which to incorporate your requests.

The remark was OBVIOUSLY directed toward the MS AUTHORS, ya dope.

Use a little common sense.

 

[Gord Dibben]

Why are you directing your remarks to people who do not hang out here and
will never read your remarks?

As for me.............I just plonked you so I won't be reading your remarks
either.


Gord

 

[Patrick Molloy]

I'd not have mentioned not having the original mail if that had been there.
As it was I made the effort to find the original in Google and I responded
with what I had hoped was useful code.

to be honest, your tone is increasing aggressive

" The remark was OBVIOUSLY directed toward the MS AUTHORS, ya dope.
Use a little common sense."

Although this wasn't directed at me, for somebody such as myself and some
others that are trying to help, I'm actually starting to get just a little
ticked off.

You don't seem to be aware that those of us that respond do so freely of our
own time. We are not Microsoft employees, but do use Microsoft products,
specifically Excel in this ng, and we choose freely try to assist, through
this and other news groups anybody that has a problem.

 

[Mycelium]

Why are you directing your remarks to people who do not hang out here and
will never read your remarks?

As for me.............I just plonked you so I won't be reading your remarks
either.

Nice attempt at assistance, pussy. Oh, that's right... you made no
such attempt.

BTW, kill filter file edit session announcements are pretty petty and
lame, little boy. Grow up, Chuck, you make me want to upchuck.

 

[Mycelium]

as far as I can see, your original post, at 03:07 on 02/07/2009
has one word in the body.
That word is "WHY".
The subject "Dropdown List Add New Feature Needed" doesn't describe the
problem

It does if you actually reference the original post. There are a few
simple questions, and one that could get more complex if *we* (myself and
the assistant) were to tackle the scenario.



Quoted As follows from 6/26/2009:

Hi folks.

How can I create a button or list entry that takes the user to a cell to
add a "new item" or "new user" to a dropdown list array by way of row
insertion and cursor placement... that takes the last entry in the
existing drop down list to use as an "add new" "button" or trigger?

The event is to add a new row to an existing named array, which
automatically updates all code in the workbook, if the row is inserted
from within the existing array.

So the last entry in the drop down list array could (would) be an "Add
New" selection for the user to choose. When the fill is performed, the
action could be to test that cell content and act accordingly. The user
would get switched to the array with the cursor in the left most cell of
the newly inserted row. This also has the effect of keeping the cell
formatting as well for solid color arrays, that is.

Is this possible? I suppose a test loop that tests the cell content,or
one that tests for and grabs the content even before it makes it to a
cell. Anyway, when one selects the add new selection from the drop down
list, instead of filling that data into the cell, it performs the insert
and pop over function so that a new record can be added.

I have a sheet that has about eight small,named arrays on it and use it
as a master data sheet for a 'presentation' type sheet that draws much of
its data from lookups to the array sheet.

A simple row insertion on that sheet updates all arrays, etc. in the
entire workbook. Nice job, guys. I LIKE IT!

So, anyway, is this a good approach for expanding the contents of an
array? Is keeping all these arrays on a single sheet a good approach?
This is like yet another new form of a database configuration, in the
final analysis. Thanks for any thoughts or views.

At least one inquiring mind wants to know.

End Quoted post


So there are actually a few easy questions there, and it was passed over
several times.

 

[Patrick Molloy]

so after all this, is this topic closed or is there something outstanding?

 

[Mycelium]

Well, it would have been outstanding had you made even a meager attempt
to answer the questions. The OP was quoted to facilitate that, but you
seem to be in peanut gallery comment mode lately.

 

[Patrick Molloy]

on 4/7/2009 at 11:41 I replied with some code. Apart from you pissing off me
and my colleagues, i saw no response.

lets have a little less of the rude aggression and such - all I have been
doing is trying to help here, OK?

 

[Mycelium]

on 4/7/2009 at 11:41 I replied with some code. Apart from you pissing off me
and my colleagues, i saw no response.

lets have a little less of the rude aggression and such - all I have been
doing is trying to help here, OK?

What rude? and what aggression? "peanut gallery" is from the Mickey
Mouse Club days, so what is aggressive or rude about that?

Since the code you responded with was prior to you seeing the original
post, how could it be pertinent?