Excel: Message Box for naming a Ran

[awool]

Excel: Message Box for naming a Range
Hope someone can give me a clue here.

I have two worksheets, Sheet1 and Sheet2. In Sheet1 there will be a dynamic number of cells with some data in, always starting at A1 and only in Column A. I have code that copies these cells and pastes them into Sheet2 Column H at the next empty cell. What I would like to do next is to have a Message Box pop up allowing me to create a Named Range for these cells that have just been pasted into Sheet2 Column H.

Any help greatly appreciated.

Regards
Awool

 

[combo]

The simplest interaction with the user can be via excel or vba InputBox. You need some testing for proper name string, if passed, add Name to Names collection, with Name and RefersTo arguments corresponding to entered text and copied data range.

combo

 

[SkipVought]

hi,

This is a very strange way to use Named Ranges. It is not at all a standard means of Naming and Using Named Ranges.

Please explain your objective and there may be a better approach.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[awool]

Thanks Combo. I did mean 'Input Box' of course. My problem is I don't know how to code the process of identifying the range once it has been posted into column H and then passing it to the input box for Naming????

 

[awool]

Hi Skip,
OK the cells that are copied from one sheet to another contain local file locations for audio files. Every time the cells are copied, that group of Audio files become in effect a playlist. The named ranges will then appear in a list box for selection at a later date.

Hope that helps

Awool

 

[SkipVought]

Your table needs an additional column for PlayList. Then an AutoFilter selection or a parameter query will get you the tunes in your play list.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[awool]

Hmmmm.......I may not have explained things very well.....or it maybe that I have just woken up.

I will try and describe the full scenario.

Sheet1 is used to temporarily hold file locations of audio files that will be played by a WMP. Sheet1 Column A is populated via a selection form which allows the me to search for audio files. Once a file is located it is transferred to Sheet1 Column A next blank cell. This process is repeated until I have all the files required. The WMP can then play the files sequentially starting at Sheet1 Cell A1.

What I am now doing, if required, is manually copying the file locations in Sheet1 column A and pasting them into Sheet2 Column H next blank cell. I then create a 'Name' for those 'just pasted cells'. (IT'S THIS BIT THAT I WANT TO AUTOMATE)

I have another userform with one listbox which is populated by code that generates a list of 'Names' in Sheet2 Column H (effectively a list of playlists) I can then click a 'Name' in the listbox which will select the cells associated with that 'Name', copy them and paste them into Sheet1 Column A starting at cell A1 ready to be played.

I have used an Excel 'Player' designed by a guy called AyrA as a basis but wanted to take it a lot further. So Sheet1 and the player is AyrA's.....everything else I have added.

Hope you can help. I confess I didn't understand your original suggestion

Regards
awool

 

[SkipVought]

So I'm telling you that with a composite TABLE in Sheet2 that ALSO has a column for PlayListName, you can then, 1) generate a PlayList List of unique PlayList Names from which you 2) can choose any specific PlayList Name, via an in-cell Data > Validation Drop Down Box, thereby 3) triggering a query to populate your Sheet1 PlayList. You don't need to go through this copy-and-name-a-range mickey mouse foolishness. It will become a nightmare when you attempt to maintain and modify your PlayLists. What I'm suggesting is a tried and true means of selecting a subset of data for some process.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

When you copy range, you know its size. You can use its size in Resize method applied to first cell in pasted range to get the whole range. Next use resized range as RefersTo argument when you add name.

combo

 

[awool]

Skip,
Let me first apologise for my 'mickey-mouse foolishness' as I had no idea that what I was trying to do was that funny. However in view of your comments and my inability to understand or carry out what you are suggesting as my knowledge and ability is still at the cartoon stage I think I will give up this project as a bad job and perhaps look for something more useful to do with my life like slowly drink a bottle of 25 year old single malt whisky. My life is now too short to worry about such things.

I would like to thank you for your extremely useful help in the past both in this forum and those to do with Access. I have learnt a lot and really have appreciated your assistance.



Many thanks

 

[SkipVought]

Permit me to explain what 'mickey mouse foolishness' might be. You discover a novel feature and then try to exploit it for your benefit. Subsequently you discover that things have changed and the new feature isn't as robust as you might have thought and now you're up the creek without a paddle.

The Range Names feature is a great feature and a great tool for analyzing and manipulating data. But if the data related to the range/name is not itself anchored by OTHER DATA, thing can go terribly wrong. You know, some anonymous butterfly in Guatemala, flaps it's wings and 35:07:42 later you wake up and flip on your playlist and it's not what you expect: 'something' scrambled your carefully copy/pasted table of playlists and all you have is ranges that refer to particular playlists but the actual data has been tampered with.

Oh well, it's not the end of the world. "What me worry!" Sure, not you. But I've been in the chair on a Monday morning, and 'something' happened over the weekend and the boss's boss's boss is demanding answers. So I'm not depending on floating unanchored range names. As I previously stated, "It will become a nightmare when you attempt to maintain and modify your PlayLists." This is a professional forum and the advice I try to dispense is based on several decades of experience in production control. If you want to play around with features using unrecommended methods, have at it. My advice is fallible and at no extra charge.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[awool]

Skip.......I apologise if I came across as sarcastic, it was not my intention and I am and have been genuinely grateful for all your help as up until now I have understood and implemented almost everything you have given me......mainly in Access.

However your latest explanation made me realise that although I still enjoy dabbling in Excel and Access my knowledge and understanding of how to do things the 'correct' way are so far away that perhaps I should not be wasting your time and others like you who are experienced professionals whose time is better spent dealing with problems/issues from others whose knowledge and experience is a lot closer to yours than mine is.

I hope this is not patronising either as I am always amazed at what you and others can make these applications do and in another life maybe I would have gone down that path. Sadly I am now too old and not in the best of health to start learning afresh or worrying about something that I don't understand. Hence my previous comments.

The project is scrapped and I am hanging up my laptop (as far as Excel is concerned)

Keep up the good work

Best regards Awool

 

[SkipVought]

awool...I am so passionate about Excel and what a great and versatile tool it is, that I come across as a curmudgeon with a burr up the butt. I'll be [sup]3/4[/sup] of a century in less than a year and have already experienced not wanting to learn a new system or application.

But now, I get to sleep in late, if I so desire, and sip coffee in my sunroom as I browse the links, read my iBooks, watch Judge Judy stick it to dead beats, then do a bit of gardening, work in my shop, visit the grand and great grandkids, do some flea market jumping (it's what feas do), drive to the store to fill the larder and a variety of other tasks.

I hope your health improves or at least permits you to enjoy some of life's later rewards. Thank you for your kind words. It is, indeed, a reward to know that you have benefited from some of the tips I have posted. I'm just repeating methods that have worked for me. Nothing original here. I, too, have benefited greatly from the wisdom of others and stand on the shoulders of myriads of giants who have had original thoughts, or who at least are "thinking God's thoughts after Him", to paraphrase Kepler, who could run ellipses around me.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]