×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Using a SpinButton to Cycle Through A Worksheet Range.
2

Using a SpinButton to Cycle Through A Worksheet Range.

Using a SpinButton to Cycle Through A Worksheet Range.

(OP)
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.

RE: Using a SpinButton to Cycle Through A Worksheet Range.

What code have you tried so far?

RE: Using a SpinButton to Cycle Through A Worksheet Range.

(OP)
My very inadequate code is:

CODE -->

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.

RE: Using a SpinButton to Cycle Through A Worksheet Range.

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:

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
 

Spinner in E2.

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

The VBA:

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Using a SpinButton to Cycle Through A Worksheet Range.

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

CODE -->

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close