ActiveX ComboBox Default Value changeable

[CJS548]

Hi,
I've been struggling with this problem for awhile.
I've made a form out of ActiveX controls, vba codes etc.
I am almost zero knowledge about vba.

I've made a combo box, named cmbDate.
ListFillRange of the combo box is Q11:Q13, where there are formulas in this cells:
Q11: =TEXT(TODAY(),"dd/mm/yyyy")
Q12: =TEXT(TODAY()-1,"dd/mm/yyyy")
Q13: =TEXT(TODAY()+1,"dd/mm/yyyy")
Also, I have this code running in vba for this combo box:

 Private Sub cmbDate_Change()    ActiveSheet.cmbDate.Value = Format(ActiveSheet.cmbDate.Value, "dd/mm/yyyy")
    
End Sub

What I want is, when a user start using the form, the combo box for date will have the Q11 value in there ready, because most of the time, a user will only use today's date, and occasionally tomorrow's or yesterday's.
So if the cmbDate can be default as today's date which is at Q11, and when he wants to change the value he can simply just press down the box and pick another date, it will be much user friendly.

I've tried:

 Private Sub cmbDate_Change()    ActiveSheet.cmbDate.Value = Format(ActiveSheet.cmbDate.Value, "dd/mm/yyyy")
    cmbDate.Value = cmbDate.List(0)
End Sub

But that will not give me option to change the value if I wanted to change, and when I press other dates in the box, it will still goes to 'today'.

Please advice me!
Thanks!

 

[James006]

Hello,

First step is to show your UserForm

Second step is to populate your UserForm with : Private Sub UserForm_Initialize()

Hope this will help

 

[DanteAmor]

Put the following in the events of ThisWorkbook. Change "Sheet1" to the name of your sheet.

Private Sub Workbook_Open()
  Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").cmbDate.ListIndex = 0
End Sub


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  If Sh.Name = "[COLOR=#ff0000]Sheet1[/COLOR]" Then
    Sheets(Sh.Name).cmbDate.ListIndex = 0
  End If
End Sub

IN THISWORKBOOK
Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will openthe Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Save the workbook as a macro-enabled file, close it and then re-open it.

 

[CJS548]

Put the following in the events of ThisWorkbook. Change "Sheet1" to the name of your sheet.

Private Sub Workbook_Open()
  Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").cmbDate.ListIndex = 0
End Sub


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  If Sh.Name = "[COLOR=#ff0000]Sheet1[/COLOR]" Then
    Sheets(Sh.Name).cmbDate.ListIndex = 0
  End If
End Sub

IN THISWORKBOOK
Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will openthe Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Save the workbook as a macro-enabled file, close it and then re-open it.

This works, thanks!
ADDITIONAL QUESTION:
1:
Can the display value in the box shown as 'last selected'?
Like if i choose 'the second value' in the box now, then after i 'save and reset' the form, the value in cmbDate will still show 'the second value'? instead of keep resetting to the first value like it ran on the code?
2:
When the form been reset, the value in all the text box and combo box will be saved to another sheets call "DATA", but the cmbDate box will not copied to the sheet as date, but it show as text format, meaning the default alignment is starting on the left, not the right like any other date format should be.
This is the code i use for saving the data:

Private Sub cmdSave_Click()    
    
    Application.ScreenUpdating = False
    
    Dim iRow As Long
    
    iRow = Sheets("DATA").Range("A104857").End(xlUp).Row + 1
            
    If ValidateForm = True Then
    
        With ThisWorkbook.Sheets("DATA")
        
            .Range("A" & iRow).Value = iRow = 1
            .Range("B" & iRow).Value = txtNumber.Value
            .Range("C" & iRow).Value = cmbDate.Value
            .Range("D" & iRow).Value = txtKG.Value
            .Range("E" & iRow).Value = cmbIssuer.Value
            .Range("F" & iRow).Value = txtSorter.Value
            .Range("G" & iRow).Value = txtZone.Value
        
        End With
        Call Reset
    Else
        Application.ScreenUpdating = True
        Exit Sub
    End If
        
        Application.ScreenUpdating = True
        
End Sub

 

[DanteAmor]

This works, thanks!
ADDITIONAL QUESTION:
1:
Can the display value in the box shown as 'last selected'?
Like if i choose 'the second value' in the box now, then after i 'save and reset' the form, the value in cmbDate will still show 'the second value'? instead of keep resetting to the first value like it ran on the code?

This point is contrary to the original request.

the combo box for date will have the Q11 value in there ready, because most of the time, a user will only use today's date, and occasionally tomorrow's or yesterday's.
So if the cmbDate can be default as today's date which is at Q11, and when he wants to change the value he can simply just press down the box and pick another date, it will be much user friendly.

However, you could do it, it is complicated, you would have to store the data somewhere on the sheet in an auxiliary cell; Then at the moment of starting evaluate if you want today or the date of the auxiliary cell.

2:
When the form been reset, the value in all the text box and combo box will be saved to another sheets call "DATA", but the cmbDate box will not copied to the sheet as date, but it show as text format, meaning the default alignment is starting on the left, not the right like any other date format should be.

For point 2, try this

Change this line:

            .Range("C" & iRow).Value = cmbDate.Value

To this:

            .Range("C" & iRow).Value = [COLOR=#0000ff]cdate[/COLOR](cmbDate.Value)

 

[CJS548]

Sorry, a little edit,
that code works when i open the workbook, but after it saves and resets, it won't show the default value...

 

[DanteAmor]

You have to put this line after doing some activity to return to the value.

[COLOR=#333333]Sheets(Sh.Name).cmbDate.ListIndex = 0[/COLOR]

 

[CJS548]

The Date transferring to the DATA sheets works well! Thanks!
That code is already in the workbook code, but still after save or reset, it will be blank, has it got to do with my save or reset code?

  Function Reset()
 
    Application.ScreenUpdating = False
    
    txtNumber.Value = ""
    txtNumber.BackColor = vbWhite
    
    txtKG.Value = ""
    txtKG.BackColor = vbWhite
    
    txtSorter.Value = ""
    txtSorter.BackColor = vbWhite
    
    cmbDate.Value = ""
    cmbDate.BackColor = vbWhite
    
    cmbIssuer.Value = ""
    cmbIssuer.BackColor = vbWhite
    
    txtZone.Value = ""
    txtZone.BackColor = vbWhite
    
    Application.ScreenUpdating = True
    
End Function

 

[DanteAmor]

Add the line to the end of function reset.

 

[DanteAmor]

The Date transferring to the DATA sheets works well! Thanks!
That code is already in the workbook code, but still after save or reset, it will be blank, has it got to do with my save or reset code?

  Function Reset()
    cmbDate.Value = ""

yes, in that line you are deleting the date