I created a user form to record data entries on excel. I used 2 frames, 1 for data entry with text boxes, combo boxes. 2nd frame is for listing the data entered. I have created a spin button to cycle through excel data. This data has stating & finishing date & time. While I am able to fetch date in correct format, I am unable to get time in hh:mm format, rather I am getting it in decimal. How to get it. Following spin button Code used
Private Sub SpinButton1_Change()
Dim s1, s2
Dim cAdd1 As String
Dim cAdd2 As String
Dim M As VbMsgBoxResult
cAdd1 = "L"
cAdd2 = "AA"
If SpinButton1.Value > 1 Then
s2 = SpinButton1.Value
s1 = "A" & s2
TextBoxEntry.ControlSource = s1
s1 = "B" & s2
ComboBoxOperator.ControlSource = s1
s1 = "C" & s2
TextSales.ControlSource = s1
s1 = "D" & s2
TextCustomer.ControlSource = s1
s1 = "E" & s2
TextJobNo.ControlSource = s1
s1 = "F" & s2
TextTitle.ControlSource = s1
s1 = "G" & s2
TextPagestxt.ControlSource = s1
s1 = "H" & s2
TextPagescvr.ControlSource = s1
s1 = "I" & s2
TextJobsize.ControlSource = s1
s1 = "J" & s2
TextColor.ControlSource = s1
s1 = "K" & s2
ComboBoxDate.ControlSource = s1
's1 = "L" & s2
'ComboBoxTime.ControlSource = s1 ' Giving time in decimal
Worksheets("Sheet1").Cells(12, s2).Value = timeValue(ComboBoxTime.Text) 'Data change and circular reference came when back spin
'Range("S1").Value = timeValue(ComboBoxTime.Text)
s1 = "M" & s2
Textsplop.ControlSource = s1
s1 = "N" & s2
ComboBoxStatus.ControlSource = s1
s1 = "O" & s2
TextPPop.ControlSource = s1
s1 = "P" & s2
ComboBoxOPStatus.ControlSource = s1
s1 = "Y" & s2
ComboBoxCorrection.ControlSource = s1
s1 = "Z" & s2
ComboBoxCDate.ControlSource = s1
's1 = "AA" & s2
'ComboBoxCTime.ControlSource = s1 ' Giving time in decimal required in HH:MM
Worksheets("Sheet1").Cells(27, s2).Value = timeValue(ComboBoxCTime.Text) 'Complete row data change to time.
s1 = "V" & s2
ComboBoxJobtype.ControlSource = s1
End If
End Sub
I used control source to fetch the values but time values returned as decimal.
I tried directly using cell reference but as soon as Spin button used, all row 12 data getting converted to time
Use this
Insert the actual sheet name in the code.
For demo try this :
Add a Spin and a ComboBox to a UserForm.
On a sheet type time values in the Range("F16:F21")
Start the Userform with F5