• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Drop down option in Macro

chicky36

New Member
Hi,


I prepare Daily sales report for my organization and I made a macro for it and it does almost 70% of my work. In that macro we add this option where it ask us the date we need our report for and it goes like this in macro,


Date_value = Application.InputBox(prompt:="last working day(DDmmyy)", Type:=2)


In this window i have to put date manually and in specified format, i was wondering if there is any option in macro that this pop up window will give you a drop down option of last 5 days and instead of typing date you just have to choose one.


Thanks


Chicky
 
Welcome to Chandoo_Org


Create a userform and add one combobox. Change the name of combobox to "DateBox" in properties | name.

Put the following code in Userform module:

[pre]
Code:
Public od As DataObject
Private Sub DateBox_Change()
Dim vbmsg As VbMsgBoxResult
'Provide user option of selecting dates and have play with them
vbmsg = MsgBox("You selected :" & Me.DateBox.Value, vbYesNo)
Select Case vbmsg
Case vbYes
Set od = New DataObject
od.SetText Me.DateBox.Value
od.PutInClipboard
Me.Hide
Case vbNo
'Do nothing
End Select
End Sub

Private Sub UserForm_Initialize()
'When the userform comes up on screen dates will get loaded
With Me.DateBox
.Clear
.AddItem Date
.AddItem Date - 1
.AddItem Date - 2
.AddItem Date - 3
.AddItem Date - 4
End With
End Sub
And in your code incorporate following:

Public Sub YourCode()
'This sub utilizes the date in the userform
Dim dt As Date
UserForm1.Show 'Userform shows up
dt = CDate(UserForm1.od.GetText(1)) 'Date gets loaded thro clipboard
MsgBox "Selected Date is : " & dt
Unload UserForm1 'Now the userform usage is finished

'Resume your code
End Sub
[/pre]
 
Hi Shrivallabha,


It took me some time to understand it but it worked in my macro. Excellent work.


Thanks a lot


Chicky36
 
Back
Top