Using a SpinButton to Cycle Through A Worksheet Range. 2

[PBAPaul]

I have a problem, in Excel 2010, using the spinbutton in a form accessing a range in a worksheet.

I will try to explain the problem.

I have a range in a worksheet called "MyDrugs". It is in a column eg A1 to A20 and contains the names of medications that I have to take.

I have a UserForm that contains various elements showing date, time, location etc. What I want to do is to pair a spinbutton (sb1) to
a text box (tb1) and then use that spinbutton sb1 to show in text box tb1 the values in "MyDrugs" sequentially.

This would mean that text box tb1 would initially show "First Drug" which is in cell A1 then when the spinbutton sb1 is spun up, the value from A2 "Second Drug" is shown in the text box tb1.

My experience using a spinbutton is not great, but this programming has me absolutely flummoxed!

Any help would be greatly appreciated.

 

[DjangMan]

What code have you tried so far?

 

[PBAPaul]

My very inadequate code is:

Private Sub SpinButton1_SpinDown()
TextBox1.Text = TextBox1.Text - Worksheets("Sheet1").Cells(TextBox1 - SpinButton1.SmallChange, 1).Value
End Sub

Private Sub SpinButton1_SpinUp()
TextBox1.Text = TextBox1.Text - Worksheets("Sheet1").Cells(TextBox1 - SpinButton1.SmallChange, 1).Value
End Sub

Private Sub UserForm_Initialize()
TextBox1.Text = Worksheets("Sheet1").Cells(1, 1).Value
End Sub

I think that the value in my range in the worksheet changes via the spinner value changing the value of the location of the worksheet cell. Obviously I haven't got it!

I will be most grateful for any help that you can give Djangman.

 

[SkipVought]

Hi,

I like simple solutions. Excel is a great tool. One of the joys of Excel for a user, in my opinion, is interacting with the data. I absolutely hate having a userform in an Excel workbook. Userform? Use a database application. Excel? Let me be on the sheet, interacting with the data!

That said, here's an approach using Excel.

Table (Structured Table) on Sheet1 A1, named tDrugs:
[pre]
Drug Date Time Location
drug 1 4-Apr 8:00 loc1
drug 2 5-Apr 9:00 loc2
drug 3 6-Apr 10:00 loc3
drug 4 7-Apr 11:00 loc4
drug 5 8-Apr 12:00 loc5
drug 6 9-Apr 13:00 loc6
drug 7 10-Apr 14:00 loc7
drug 8 11-Apr 15:00 loc8
drug 9 12-Apr 16:00 loc9
drug 10 13-Apr 17:00 loc10
[/pre]

Spinner in E2.

Spinner results in F2:I2
[pre]
drug 4 Saturday, 07 Apr 2018 11:00 AM loc4
[/pre]

The VBA:

Private Sub sp1_Change()
    Dim xl As Application
    
    Set xl = Application
    
    sp1.Min = 0
    sp1.Max = [tDrugs].Rows.Count - 1
    
    [F2].Value = xl.Index([tDrugs[Drug]], sp1.Value + 1)
    [G2].Value = Format(xl.Index([tDrugs[Date]], sp1.Value + 1), "dddd, dd mmm yyyy")
    [H2].Value = Format(xl.Index([tDrugs[time]], sp1.Value + 1), "hh:mm am/pm")
    [I2].Value = xl.Index([tDrugs[Location]], sp1.Value + 1)
    
    sp1.Parent.UsedRange.EntireColumn.AutoFit
    
    Set xl = Nothing
End Sub

My result:

SIMPLE! Unencumbered! Right on a sheet!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

When I work with spinbutton, I rather use its value (Long). If the userform has also a command button to update worksheet:

Private Sub CommandButton1_Click()
With Me
    Worksheets("Sheet1").Cells(.SpinButton1.Value, 1).Value = .TextBox1
End With
End Sub

Private Sub SpinButton1_SpinDown()
Call UpdateTextBox1
End Sub

Private Sub SpinButton1_SpinUp()
Call UpdateTextBox1
End Sub

Private Sub UserForm_Initialize()
With Me.SpinButton1
    .Min = 1
    .Max = 20
    .Value = 1
End With
Me.UpdateTextBox1
End Sub

Public Sub UpdateTextBox1()
With Me
    .TextBox1.Text = Worksheets("Sheet1").Cells(.SpinButton1.Value, 1).Value
End With
End Sub

combo