How to use more space in an inputbox

I would like the user to enter a specific date eg Nov-09 and for a macro to exicute based on the date entered. I just thought an inputbox would simplify the entry process for the users who are not very excel literate. What would you suggest?

Note too that without a day portion, your entry cannot be a real date. I don't know if that is relevant to your macro.

Have you looked an option of using calendar in the excel by which you can also select dates as well rather then using a inputbox, You can download sample calendar files from the following link:-

http://www.experts-exchange.com/Q_22853372.html

Let me know if you need any further asssitance over the same.

Saurabh....

Just be aware that both of those files require controls that you or your users may not have available on their machines.

Rory good point, Here is without any library that is required in the system... ;-)

http://www.experts-exchange.com/Q_23967378.html

Saurabh...

rorya, I have been working on the userform and i've set up a ComboBox (thanks for the suggestion it is a much better option). I've been teaching myself VBA from a book and it doesn't go into detail about how to make a ComboBox work. I have everything working except the ok button (which is obviously the most important button). How do i make it run specific Macros according to the selections a user will make from the ComboBox.

Padawon-SW,

Did you got a chance to look over the questions which i refered as those contains excel files which has inbuilt calendars from it which does what you are looking for.

Saurabh...

Do you run different macros for different dates? (sounds odd to me).

The basic syntax would be something like:

Select Case Me.Listbox1.Value
   Case "Nov-09"
      call macro1
   Case "Dec-09"
      call macro2
' and so on
End Select

                                              

1:
2:
3:
4:
5:
6:
7:

Thanks, that is a huge help i will try it now!
To be honest it doesn't have to be a different macro for each date but a slight variation on a central macro. Is there an easier way of doing it?

Yes - you would rewrite the basic macro so that it takes a date (or string) as an input and then does whatever it needs to do with it. Hard to be specific without seeing the code, though.

This is the code

For each month i want a slight change to the sum formulas:

Sub TotalCost2Date()
    Range("bm8").Formula = "=sum(e8,g8)"
    Range("bm8").Copy Range("bm13:bm49,bm52:bm56,bm59:bm71,bm74:bm80,bm83,bm86")
    Range("BM13:BM49,bm52:bm56,bm59:bm71,bm74:bm80"). _
        Borders(xlInsideHorizontal).LineStyle = xlNone
'End Sub
'Sub TotalCost2Complete()
    Range("bo8").Formula = "=sum(h8:n8,s8:ae8,aj8:au8,az8:bk8)"
    Range("bo8").Copy Range("bo13:bo49,bo52:bo56,bo59:bo71,bo74:bo80,bo83,bo86")
    Range("Bo13:Bo49,bo52:bo56,bo59:bo71,bo74:bo80"). _
        Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("bo86").Borders.Weight = xlThin
End Sub

Also i have been trying to get the ComboBox userform to work and the macro you gave only takes into account 1 list. however i have two: one for the months and one for the years, how would i do that?

The worksheet should never be changed and i have locked all the cells. However what would you suggest instead of the Hardcoded ranges (just for future stuff)?

Thank you for the 2 combos solution! I really appreciate all your help!

It depends on the workbook to some extent, but ideally using named ranges would be better. You can then simply update them in the main Excel window and the code doesn't need to be changed.

Please, can you give an example using the macro i sent you?