Search code examples
excelvbatimeformatspin

Excel vba spin button fetching wrong time value and data getting changed

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

Before using Spin Button

After Using Spin Button

Solution

  • Use this

    ComboBoxCTime.Value = Format(Sheets("Your_sheet_name").Cells(s2,"L"), "hh:mm")

    Insert the actual sheet name in the code.

    For demo try this :

    Private Sub SpinButton1_Change()
Set r = ActiveSheet.Range("F16:F21")
ReDim b(r.Count, 1)
For i = 0 To r.Count - 1
b(i, 0) = Format(r.Cells(i), "hh:mm")
Next i
ComboBox1.List = b
ComboBox1.Value = Format(ActiveSheet.Cells(15 + SpinButton1.Value, "F"), "hh:mm")
End Sub

    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