ActiveX ComboBox Default Value changeable

CJS548

New Member
Joined
Dec 11, 2013
Messages
25
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:
Code:
 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:
Code:
 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!
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,949
Office Version
2007
Platform
Windows
Put the following in the events of ThisWorkbook. Change "Sheet1" to the name of your sheet.

Code:
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.
 
Last edited:

CJS548

New Member
Joined
Dec 11, 2013
Messages
25
Put the following in the events of ThisWorkbook. Change "Sheet1" to the name of your sheet.

Code:
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:
Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,949
Office Version
2007
Platform
Windows
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:
Code:
            .Range("C" & iRow).Value = cmbDate.Value
To this:
Code:
            .Range("C" & iRow).Value = [COLOR=#0000ff]cdate[/COLOR](cmbDate.Value)
 

CJS548

New Member
Joined
Dec 11, 2013
Messages
25
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,949
Office Version
2007
Platform
Windows
You have to put this line after doing some activity to return to the value.

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

CJS548

New Member
Joined
Dec 11, 2013
Messages
25
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?
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,949
Office Version
2007
Platform
Windows
Add the line to the end of function reset.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,949
Office Version
2007
Platform
Windows
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?
Code:
  Function Reset()
    cmbDate.Value = ""
yes, in that line you are deleting the date
 

Watch MrExcel Video

Forum statistics

Threads
1,100,186
Messages
5,473,011
Members
406,843
Latest member
David_Welland

This Week's Hot Topics

Top