Would like a Macro that would use a dialog box to ask which column to use and than what number (value) to copy down what ever number of rows there might be.

Try this.

It opens a dialog and the user can select the last column of data to add the List and numbers.

I can automate the task to add the the heading automatically. Let me know if it needs changing
Purcell-Test_10.xlsm

just made a little improvement.

Added error handling to checkentries have been made and allows for entering into a column not adjacent to the data
Purcell-Test_11.xlsm

Cool dialog box! I'm getting an error. Attached is screen shot. Tried both upper case and lower case letter
error.docx
errorDebug.docx

On further reading your solution I see that I click on the column, not type in the column letter. It works great! How do I pull out the macro to saveit?

The code is all in the UserForm. You can export the UserForm by right clicking on it in the VB Editor  in the Project Explorer on the left. Choose Export File, then open the workbook that you need to add the userform to and choose Import file, browse to where you exported the files to and select Import File. The export will create two file UserForm1.frm amd UserForm1.frx. When importing you will see UserForm1.frm in the import dialog. Click on it and both files will be imported to create the userform in the new workbook.

in the VB Editor You can simply drag & drop the UserForm into the destination workbook with both workbooks open.

I'm attaching a revised version. I forgot to say you can click on the top control to collapse the form whilst selecting a column.
Purcell-Test_11.xlsm

Hi Dan,

In attached sheet, when you press Fill List, it prompt you to enter numeric value, default is 1, you can type any number manually. Once numeric value is accepted, it will ask you to type Cell Reference from where you need to fill the range, default is F2, you can change to G2, H2 or anything.

Hope this helps.
Purcell-Test_10_v1.xlsm

Hi Roy. I imported the UserForm1.frm. How do I run that in the future? It doesn't show up in my Macro list

Hi Dan,

Have you tried my solution, this is what you asked initially.

I did Shums. I liked the button but same as above, how to I save it and access it

You need to add a macro, or use the Workbook_Open event as in the example

Insert a new Module  then type :

Sub ShowFrm()
UserForm1.Show
End Sub

Select allOpen in new window

Here's a couple of simple ways to run the macro

Forms Button
You can add a Forms control button in much the same way as an ActiveX button.
•      On the Developer tab, in the Controls group first click Insert, and then under Form Controls click the Button icon in Forms Controls group
•      Click the worksheet location where you want the upper-left corner of the button to appear. Use the drag handles to draw your button. You can change the size and position later.
•      The Assign Macro dialog box will immediately appear, giving you the opportunity to assign a macro to the button. You will see that the dialog box shows a list of previously created macros, along with a suggested name for the macro to be assigned to this button. The suggested name is comprised of the default name of the button itself (something like Button1) combined with the action that will start the macro (Click).
•      To finish with the Assign Macro dialog box, select a macro you want assigned to this new button and then click on OK. You can then change the caption appearing on the button by clicking your mouse within the button text and entering a new caption.
•      You have now created a button for your macro which will be run whenever anyone clicks on it with the left mouse button.
•      If you use the right mouse button instead, you will see a menu that allows you to delete the button or change the macro assigned to the button.
•      To specify the control properties of the button, right-click the button, and then click Format Control. A menu that allows you to delete the button or change the macro assigned to the button will appear. You can also change various Properties of the button here- Size, Text, Font, etc. The Properties tab is very useful, here you can choose whether the button will print or not and positioning of the control.
Use a Shape as a Button
In much the same way as a Forms Button you can assign a macro to a button (or any other object such as an image).
To start go to Ribbon -> Insert -> Shapes and select your choice of the many shapes available. I like the rounded rectangle, but choose any that you like. Now right click on the shape and then from the menu you can choose what you want to do like adding suitable text to the shape. Most importantly for this tutorial if you look further down the menu you will see Assign macro. Click and the Assign Macro Dialog Box is displayed so you can choose the macro to run as you did with the Forms Button before.

Shums a userForm is a kind of Dialog and is more professional looking than InputBoxes, quicker to input as well.

Also, my code runs more efficiently because it doesn't use a Loop!

Also, my code checks for a numeric value!

Forgot to add the example with a button.
Purcell-Test_11.xlsm

I'll get back to this, my week just kicked into overdrive

Thanks

I can't really see why  you awarded equal points for less efficient code.

Sorry Roy, I'm not good at grading.

No problem to me really, but as I said earlier Loops should be avoided, a UderForm is more professuional looking and my code does more error checking.