Date list in a message box?

[Gareth]

Hey all. Still new to VBA's and now message boxes, so this might just be a YesNo question.

Without getting into userforms, can I get a drop down list to show in a normal message box? Possibly what code would i use?

I have searched as much as my patience allows, but to no avail. I do know that one can create a userform, but it seems like a mission compared to a message box.
What I am trying to achieve though, is to have a list of the dates of the year (or maybe just the past and future 2 weeks from the current date would be better) that my end user would be able to click on and it would be added to a heading that would look like this "A SHIFT 26-Feb-2014".
I have the code to input the A SHIFT, but the date might not always be current as these guys don't always create the sheets when they are supposed to. So i want to give them the option to 'back date' the sheets if you may. I then have code taking said heading and naming the sheet, hence the date format.
Don't give me too much hell for being lazy

 

[Hui]

Yes, You can

You can use code as simple as

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
a = MsgBox("My prompt", vbOKOnly, "My Title")
b = InputBox("Enter a value here: ", "My Title", 0)
End Sub

Having said that
Adding a User Form is a very easy task and gives you heaps of flexibility

You can probably also do what you want with Data Validation

Can you post a file with your requirements?

 

[Gareth]

Hmm, that would give me an input box where i'm trying to only use mouse clicks to insert the date.
I hope posting my macro here is fine? I've just put the last half out of action for now. It will end up with out the " ' " marks in the end.

Sub New_sheets()

Dim X As VbMsgBoxResult
Dim Y As VbMsgBoxResult
'Dim Z as something like a drop down box with a list
'Dim A As another small drop down box with 3 different choices or the YesNoCancel msgbox

ActiveSheet.Copy After:=Sheets(Sheets.Count)

Start: X = MsgBox("Would you like to keep the previous data?", vbYesNo + vbQuestion + vbDefaultButton2, "Keep previous data?")
    Select Case X
        Case vbYes
        Case vbNo
            ActiveSheet.Range("K5:S39").ClearContents
    End Select
Y = MsgBox("A SHIFT = Yes. B SHIFT = No", vbYesNo, "WHICH SHIFT?")
    Select Case Y
        Case vbYes
            ActiveSheet.Range("U1").Value = "A SHIFT"
        Case vbNo
            ActiveSheet.Range("U1").Value = "B SHIFT"
    End Select

    'and then here would be the part where a drop down box is diplayed
    'and a list is presented of possible dates to choose from
    'and upon clicking the date they want, it will be deposited
    'into range("V1")

'Range("C3").Formula = "=U1 &"" ""& V1"
'ActiveSheet.Name = Range("C3").Value
'Range("C3").Copy
'Range("C3").PasteSpecial xlPasteValues

'A = MsgBox("Select the time", vbYesNoCancel, "Time selection")
    'Select Case A
        'Case vbYes
            'ActiveSheet.Range("L3").Value = " 06:00 - - 18:00"
        'case vbNo
            'ActiveSheet.Range("L3").Value = " 06:00 - - 16:00"
        'case vbCancel
            'ActiveSheet.Range("L3").Value = " 18:00 - - 06:00"
    'End Select
  
End Sub

 

[Gareth]

Also, if there's a quicker/shorter way to write the code, I'm fully open to idea's.

 

[Hui]

You can put an Active-X Combo or Index Box on the Worksheet
These are available in the Developer, Insert tab
Then make it visible / hidden as required using vba
Don't use the Form Control Controls from the Developer, Insert tab

 

[Gareth]

Hey. Ok. So i went and learnt a bit about Userforms now and attached is my endeavour to throw my previous macro's together in a single user form. Almost at the end when i get an error message. Maybe someone can help me out?
The idea behind the macro with the listbox is that you click on the date you want and it should then create a formula in another cell that would combine a previous selection from a commandbutton and the value of the listbox selection to show as one eg: A SHIFT 26-Feb-2014. But now because i don't want that formula to stay in that cell, i do the copy and paste value in that same cell. This then is used to name the sheet as well.
My problem comes into play, for some reason, with my button to clean things up and close the userform (Commandbutton 7). I get runtime error "1004" Copy method of range class failed and it points to the copy line happening with the listbox event. I need help to sort that out and any suggestions to this better.
ps: I disabled the copy sheet line to stop from creating lots of sheets for now.

 

[Gareth]

I think I may have found a work around;
I moved the bunch of lines that copy and paste from the listbox sub to the Commandbutton7 sub. The Button that is used to end the userform.

 

[Gareth]

Hmmm, there needs to be an "edit message" so you can update your messages without having to multipost...
Attached should be the almost final product. I just need to redo the form sheet to look like the original company standard and do some naming of buttons in the userform.
But one last query if anyone can help on this: Error checking and handling, i find on my final button click (CommandButton7) that if there's and error eg. i forgot to select something like the date, is there something that can be done to counteract this? Or to say, ...if there's an error, pop up a message box saying "You forgot something" then end the click event without unloading the form or carrying on with the macro that is programmed for the button click?