Spinbutton help required
 

Hello everyone

Can anyone direct me to a site where I can learn how to code a spin button?
I have not used one before and I have not got a clue where to start!

I want to use it in the following manner,

I have an Excel database that has a userform for entering and retreiving
data. The form has several fields - one of which is TextBox2 which shows
the entry number.
I would like to add a spinner to TextBox2 so I can select the previous or
next entry and show the details in the other fields.

I would be glad of any advice!

Kenny

Win 2000 and Millennium
Excel 97 and 2000

Spinbutton help required
 

There is no trick to it.

Use the spinup and spindown events to increment an index into your range and
retrieve the value. Assign the retrieved value to the textbox. Set the
min and max values so the spinbutton will not go outside your range.

Private Sub SpinButton1_SpinUp()
idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With
End Sub
Private Sub SpinButton1_SpinDown()
idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With
End Sub


Private Sub UserForm_Initialize()
SpinButton1.Max = 20
SpinButton1.Min = 1
SpinButton1.Value = 10
End Sub


The buttons in this case work opposite of the rows (spindown increases the
row addressed). However, with increasing values in the cell, it works OK
depending on your perspective. However, if you want to have spinup decrease
the row (arrow points in the direction of row change), you can use

Private Sub SpinButton1_SpinUp()
idex = 21 - SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With
End Sub

as an example (similar change in SpinDown).


--
Regards,
Tom Ogilvy

"N E Body" wrote in message
...
Hello everyone

Can anyone direct me to a site where I can learn how to code a spin
button?
I have not used one before and I have not got a clue where to start!

I want to use it in the following manner,

I have an Excel database that has a userform for entering and retreiving
data. The form has several fields - one of which is TextBox2 which shows
the entry number.
I would like to add a spinner to TextBox2 so I can select the previous or
next entry and show the details in the other fields.

I would be glad of any advice!

Kenny

Win 2000 and Millennium
Excel 97 and 2000

Spinbutton help required
 

Kenny,

Spin buttons are usually used for incremental counters, to get a value that
is easily changed with a mouse click. As a record selector, it might change
too quickly.

Why not just add command buttons with captions of '' and '<<' and trap the
click event to get the next/previous record.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"N E Body" wrote in message
...
Hello everyone

Can anyone direct me to a site where I can learn how to code a spin
button?
I have not used one before and I have not got a clue where to start!

I want to use it in the following manner,

I have an Excel database that has a userform for entering and retreiving
data. The form has several fields - one of which is TextBox2 which shows
the entry number.
I would like to add a spinner to TextBox2 so I can select the previous or
next entry and show the details in the other fields.

I would be glad of any advice!

Kenny

Win 2000 and Millennium
Excel 97 and 2000

Spinbutton help required
 

Many thanks Tom

I have got it working on my userform apart from 1 problem which I cannot
fix.

If I enter a number into TextBox2 then click spinup/down I would like the
number in textbox2 to advance/decrease by one.
With my code TextBox2 is populated with a low number then spins from that
number!

How do I correct this?

Sample of my code below

Many thanks

Kenny

Private Sub UserForm_initialize()

SpinButton1.Max = 3000
SpinButton1.Min = 4
SpinButton1.Value = 4

End Sub



Private Sub SpinButton1_SpinUp()

idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With

ans = LogEntry.TextBox2.Value
res = Application.Match(CLng(ans), Range("A4:A3000"), 0)
Range("A4:A3000")(res).Activate

IdentRetrieve1

End Sub



Private Sub SpinButton1_SpinDown()

idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With

ans = LogEntry.TextBox2.Value
res = Application.Match(CLng(ans), Range("A4:A3000"), 0)
Range("A4:A3000")(res).Activate

IdentRetrieve1

End Sub






"Tom Ogilvy" wrote in message
...
There is no trick to it.

Use the spinup and spindown events to increment an index into your range
and
retrieve the value. Assign the retrieved value to the textbox. Set the
min and max values so the spinbutton will not go outside your range.

Private Sub SpinButton1_SpinUp()
idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With
End Sub
Private Sub SpinButton1_SpinDown()
idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With
End Sub


Private Sub UserForm_Initialize()
SpinButton1.Max = 20
SpinButton1.Min = 1
SpinButton1.Value = 10
End Sub


The buttons in this case work opposite of the rows (spindown increases the
row addressed). However, with increasing values in the cell, it works OK
depending on your perspective. However, if you want to have spinup
decrease
the row (arrow points in the direction of row change), you can use

Private Sub SpinButton1_SpinUp()
idex = 21 - SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With
End Sub

as an example (similar change in SpinDown).


--
Regards,
Tom Ogilvy

"N E Body" wrote in message
...
Hello everyone

Can anyone direct me to a site where I can learn how to code a spin
button?
I have not used one before and I have not got a clue where to start!

I want to use it in the following manner,

I have an Excel database that has a userform for entering and retreiving
data. The form has several fields - one of which is TextBox2 which
shows
the entry number.
I would like to add a spinner to TextBox2 so I can select the previous
or
next entry and show the details in the other fields.

I would be glad of any advice!

Kenny

Win 2000 and Millennium
Excel 97 and 2000

Spinbutton help required
 

Just add this

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

SpinButton1.Value = TextBox1.Text
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"N E Body" wrote in message
...
Many thanks Tom

I have got it working on my userform apart from 1 problem which I cannot
fix.

If I enter a number into TextBox2 then click spinup/down I would like the
number in textbox2 to advance/decrease by one.
With my code TextBox2 is populated with a low number then spins from that
number!

How do I correct this?

Sample of my code below

Many thanks

Kenny

Private Sub UserForm_initialize()

SpinButton1.Max = 3000
SpinButton1.Min = 4
SpinButton1.Value = 4

End Sub



Private Sub SpinButton1_SpinUp()

idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With

ans = LogEntry.TextBox2.Value
res = Application.Match(CLng(ans), Range("A4:A3000"), 0)
Range("A4:A3000")(res).Activate

IdentRetrieve1

End Sub



Private Sub SpinButton1_SpinDown()

idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With

ans = LogEntry.TextBox2.Value
res = Application.Match(CLng(ans), Range("A4:A3000"), 0)
Range("A4:A3000")(res).Activate

IdentRetrieve1

End Sub






"Tom Ogilvy" wrote in message
...
There is no trick to it.

Use the spinup and spindown events to increment an index into your range
and
retrieve the value. Assign the retrieved value to the textbox. Set
the
min and max values so the spinbutton will not go outside your range.

Private Sub SpinButton1_SpinUp()
idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With
End Sub
Private Sub SpinButton1_SpinDown()
idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With
End Sub


Private Sub UserForm_Initialize()
SpinButton1.Max = 20
SpinButton1.Min = 1
SpinButton1.Value = 10
End Sub


The buttons in this case work opposite of the rows (spindown increases
the
row addressed). However, with increasing values in the cell, it works
OK
depending on your perspective. However, if you want to have spinup
decrease
the row (arrow points in the direction of row change), you can use

Private Sub SpinButton1_SpinUp()
idex = 21 - SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With
End Sub

as an example (similar change in SpinDown).


--
Regards,
Tom Ogilvy

"N E Body" wrote in message
...
Hello everyone

Can anyone direct me to a site where I can learn how to code a spin
button?
I have not used one before and I have not got a clue where to start!

I want to use it in the following manner,

I have an Excel database that has a userform for entering and
retreiving
data. The form has several fields - one of which is TextBox2 which
shows
the entry number.
I would like to add a spinner to TextBox2 so I can select the previous
or
next entry and show the details in the other fields.

I would be glad of any advice!

Kenny

Win 2000 and Millennium
Excel 97 and 2000

Spinbutton help required
 

Thanks Bob

If I type in 40 then click spinup it returns 38??
This is because it is refering to row numbers and 38 is row 41 columnA
value! (it is working because it has advanced one row!)

Is it possible to advance 2 rows down

something like
SpinButton1.Value = TextBox1.Text+2

TIA

Kenny


"Bob Phillips" wrote in message
...
Just add this

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

SpinButton1.Value = TextBox1.Text
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"N E Body" wrote in message
...
Many thanks Tom

I have got it working on my userform apart from 1 problem which I cannot
fix.

If I enter a number into TextBox2 then click spinup/down I would like
the
number in textbox2 to advance/decrease by one.
With my code TextBox2 is populated with a low number then spins from
that
number!

How do I correct this?

Sample of my code below

Many thanks

Kenny

Private Sub UserForm_initialize()

SpinButton1.Max = 3000
SpinButton1.Min = 4
SpinButton1.Value = 4

End Sub



Private Sub SpinButton1_SpinUp()

idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With

ans = LogEntry.TextBox2.Value
res = Application.Match(CLng(ans), Range("A4:A3000"), 0)
Range("A4:A3000")(res).Activate

IdentRetrieve1

End Sub



Private Sub SpinButton1_SpinDown()

idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With

ans = LogEntry.TextBox2.Value
res = Application.Match(CLng(ans), Range("A4:A3000"), 0)
Range("A4:A3000")(res).Activate

IdentRetrieve1

End Sub






"Tom Ogilvy" wrote in message
...
There is no trick to it.

Use the spinup and spindown events to increment an index into your
range
and
retrieve the value. Assign the retrieved value to the textbox. Set
the
min and max values so the spinbutton will not go outside your range.

Private Sub SpinButton1_SpinUp()
idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With
End Sub
Private Sub SpinButton1_SpinDown()
idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With
End Sub


Private Sub UserForm_Initialize()
SpinButton1.Max = 20
SpinButton1.Min = 1
SpinButton1.Value = 10
End Sub


The buttons in this case work opposite of the rows (spindown increases
the
row addressed). However, with increasing values in the cell, it works
OK
depending on your perspective. However, if you want to have spinup
decrease
the row (arrow points in the direction of row change), you can use

Private Sub SpinButton1_SpinUp()
idex = 21 - SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With
End Sub

as an example (similar change in SpinDown).


--
Regards,
Tom Ogilvy

"N E Body" wrote in message
...
Hello everyone

Can anyone direct me to a site where I can learn how to code a spin
button?
I have not used one before and I have not got a clue where to start!

I want to use it in the following manner,

I have an Excel database that has a userform for entering and
retreiving
data. The form has several fields - one of which is TextBox2 which
shows
the entry number.
I would like to add a spinner to TextBox2 so I can select the
previous
or
next entry and show the details in the other fields.

I would be glad of any advice!

Kenny

Win 2000 and Millennium
Excel 97 and 2000

Spinbutton help required
 

Don't understand what you are saying.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"N E Body" wrote in message
...
Thanks Bob

If I type in 40 then click spinup it returns 38??
This is because it is refering to row numbers and 38 is row 41 columnA
value! (it is working because it has advanced one row!)

Is it possible to advance 2 rows down

something like
SpinButton1.Value = TextBox1.Text+2

TIA

Kenny


"Bob Phillips" wrote in message
...
Just add this

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

SpinButton1.Value = TextBox1.Text
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"N E Body" wrote in message
...
Many thanks Tom

I have got it working on my userform apart from 1 problem which I
cannot
fix.

If I enter a number into TextBox2 then click spinup/down I would like
the
number in textbox2 to advance/decrease by one.
With my code TextBox2 is populated with a low number then spins from
that
number!

How do I correct this?

Sample of my code below

Many thanks

Kenny

Private Sub UserForm_initialize()

SpinButton1.Max = 3000
SpinButton1.Min = 4
SpinButton1.Value = 4

End Sub



Private Sub SpinButton1_SpinUp()

idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With

ans = LogEntry.TextBox2.Value
res = Application.Match(CLng(ans), Range("A4:A3000"), 0)
Range("A4:A3000")(res).Activate

IdentRetrieve1

End Sub



Private Sub SpinButton1_SpinDown()

idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With

ans = LogEntry.TextBox2.Value
res = Application.Match(CLng(ans), Range("A4:A3000"), 0)
Range("A4:A3000")(res).Activate

IdentRetrieve1

End Sub






"Tom Ogilvy" wrote in message
...
There is no trick to it.

Use the spinup and spindown events to increment an index into your
range
and
retrieve the value. Assign the retrieved value to the textbox.
Set
the
min and max values so the spinbutton will not go outside your range.

Private Sub SpinButton1_SpinUp()
idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With
End Sub
Private Sub SpinButton1_SpinDown()
idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With
End Sub


Private Sub UserForm_Initialize()
SpinButton1.Max = 20
SpinButton1.Min = 1
SpinButton1.Value = 10
End Sub


The buttons in this case work opposite of the rows (spindown
increases
the
row addressed). However, with increasing values in the cell, it
works
OK
depending on your perspective. However, if you want to have spinup
decrease
the row (arrow points in the direction of row change), you can use

Private Sub SpinButton1_SpinUp()
idex = 21 - SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With
End Sub

as an example (similar change in SpinDown).


--
Regards,
Tom Ogilvy

"N E Body" wrote in message
...
Hello everyone

Can anyone direct me to a site where I can learn how to code a
spin
button?
I have not used one before and I have not got a clue where to
start!

I want to use it in the following manner,

I have an Excel database that has a userform for entering and
retreiving
data. The form has several fields - one of which is TextBox2
which
shows
the entry number.
I would like to add a spinner to TextBox2 so I can select the
previous
or
next entry and show the details in the other fields.

I would be glad of any advice!

Kenny

Win 2000 and Millennium
Excel 97 and 2000

Spinbutton help required
 

For some reason the code you gave me returned a number two less than what
was entered.

The number I entered is an ID number in column A

I presume Excel sees this numberin the TextBox as a Row number as my ID
number (x) is in Row (x-2)

Oddly enough I answered my own question in my last thread - when I added +2
to your code it returned the value I expected.

Many thanks again

Kenny
and yes N E Body is Kennyatwork, Kennyathome etc of www.crathornehall.com
fame (you have assisted me many times!!!)

"Bob Phillips" wrote in message
...
Don't understand what you are saying.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"N E Body" wrote in message
...
Thanks Bob

If I type in 40 then click spinup it returns 38??
This is because it is refering to row numbers and 38 is row 41 columnA
value! (it is working because it has advanced one row!)

Is it possible to advance 2 rows down

something like
SpinButton1.Value = TextBox1.Text+2

TIA

Kenny


"Bob Phillips" wrote in message
...
Just add this

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

SpinButton1.Value = TextBox1.Text
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"N E Body" wrote in message
...
Many thanks Tom

I have got it working on my userform apart from 1 problem which I
cannot
fix.

If I enter a number into TextBox2 then click spinup/down I would
like
the
number in textbox2 to advance/decrease by one.
With my code TextBox2 is populated with a low number then spins from
that
number!

How do I correct this?

Sample of my code below

Many thanks

Kenny

Private Sub UserForm_initialize()

SpinButton1.Max = 3000
SpinButton1.Min = 4
SpinButton1.Value = 4

End Sub



Private Sub SpinButton1_SpinUp()

idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With

ans = LogEntry.TextBox2.Value
res = Application.Match(CLng(ans), Range("A4:A3000"), 0)
Range("A4:A3000")(res).Activate

IdentRetrieve1

End Sub



Private Sub SpinButton1_SpinDown()

idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With

ans = LogEntry.TextBox2.Value
res = Application.Match(CLng(ans), Range("A4:A3000"), 0)
Range("A4:A3000")(res).Activate

IdentRetrieve1

End Sub






"Tom Ogilvy" wrote in message
...
There is no trick to it.

Use the spinup and spindown events to increment an index into your
range
and
retrieve the value. Assign the retrieved value to the textbox.
Set
the
min and max values so the spinbutton will not go outside your
range.

Private Sub SpinButton1_SpinUp()
idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With
End Sub
Private Sub SpinButton1_SpinDown()
idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With
End Sub


Private Sub UserForm_Initialize()
SpinButton1.Max = 20
SpinButton1.Min = 1
SpinButton1.Value = 10
End Sub


The buttons in this case work opposite of the rows (spindown
increases
the
row addressed). However, with increasing values in the cell, it
works
OK
depending on your perspective. However, if you want to have
spinup
decrease
the row (arrow points in the direction of row change), you can use

Private Sub SpinButton1_SpinUp()
idex = 21 - SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With
End Sub

as an example (similar change in SpinDown).


--
Regards,
Tom Ogilvy

"N E Body" wrote in message
...
Hello everyone

Can anyone direct me to a site where I can learn how to code a
spin
button?
I have not used one before and I have not got a clue where to
start!

I want to use it in the following manner,

I have an Excel database that has a userform for entering and
retreiving
data. The form has several fields - one of which is TextBox2
which
shows
the entry number.
I would like to add a spinner to TextBox2 so I can select the
previous
or
next entry and show the details in the other fields.

I would be glad of any advice!

Kenny

Win 2000 and Millennium
Excel 97 and 2000

Spinbutton help required
 

Kenny (or whatever the N E stands for<g),

It works good for me (doesn't it always).

Post the full code and a bit of description an d I'll try to help.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"N E Body" wrote in message
...
For some reason the code you gave me returned a number two less than what
was entered.

The number I entered is an ID number in column A

I presume Excel sees this numberin the TextBox as a Row number as my ID
number (x) is in Row (x-2)

Oddly enough I answered my own question in my last thread - when I added
+2
to your code it returned the value I expected.

Many thanks again

Kenny
and yes N E Body is Kennyatwork, Kennyathome etc of www.crathornehall.com
fame (you have assisted me many times!!!)

"Bob Phillips" wrote in message
...
Don't understand what you are saying.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"N E Body" wrote in message
...
Thanks Bob

If I type in 40 then click spinup it returns 38??
This is because it is refering to row numbers and 38 is row 41 columnA
value! (it is working because it has advanced one row!)

Is it possible to advance 2 rows down

something like
SpinButton1.Value = TextBox1.Text+2

TIA

Kenny


"Bob Phillips" wrote in message
...
Just add this

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

SpinButton1.Value = TextBox1.Text
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"N E Body" wrote in message
...
Many thanks Tom

I have got it working on my userform apart from 1 problem which I
cannot
fix.

If I enter a number into TextBox2 then click spinup/down I would
like
the
number in textbox2 to advance/decrease by one.
With my code TextBox2 is populated with a low number then spins
from
that
number!

How do I correct this?

Sample of my code below

Many thanks

Kenny

Private Sub UserForm_initialize()

SpinButton1.Max = 3000
SpinButton1.Min = 4
SpinButton1.Value = 4

End Sub



Private Sub SpinButton1_SpinUp()

idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With

ans = LogEntry.TextBox2.Value
res = Application.Match(CLng(ans), Range("A4:A3000"), 0)
Range("A4:A3000")(res).Activate

IdentRetrieve1

End Sub



Private Sub SpinButton1_SpinDown()

idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With

ans = LogEntry.TextBox2.Value
res = Application.Match(CLng(ans), Range("A4:A3000"), 0)
Range("A4:A3000")(res).Activate

IdentRetrieve1

End Sub






"Tom Ogilvy" wrote in message
...
There is no trick to it.

Use the spinup and spindown events to increment an index into
your
range
and
retrieve the value. Assign the retrieved value to the textbox.
Set
the
min and max values so the spinbutton will not go outside your
range.

Private Sub SpinButton1_SpinUp()
idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With
End Sub
Private Sub SpinButton1_SpinDown()
idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With
End Sub


Private Sub UserForm_Initialize()
SpinButton1.Max = 20
SpinButton1.Min = 1
SpinButton1.Value = 10
End Sub


The buttons in this case work opposite of the rows (spindown
increases
the
row addressed). However, with increasing values in the cell, it
works
OK
depending on your perspective. However, if you want to have
spinup
decrease
the row (arrow points in the direction of row change), you can
use

Private Sub SpinButton1_SpinUp()
idex = 21 - SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With
End Sub

as an example (similar change in SpinDown).


--
Regards,
Tom Ogilvy

"N E Body" wrote in message
...
Hello everyone

Can anyone direct me to a site where I can learn how to code a
spin
button?
I have not used one before and I have not got a clue where to
start!

I want to use it in the following manner,

I have an Excel database that has a userform for entering and
retreiving
data. The form has several fields - one of which is TextBox2
which
shows
the entry number.
I would like to add a spinner to TextBox2 so I can select the
previous
or
next entry and show the details in the other fields.

I would be glad of any advice!

Kenny

Win 2000 and Millennium
Excel 97 and 2000

Spinbutton help required
 

Hello Bob

Thanks for your offer of help but I must be getting the hang of this VBA
thing as I think its working OK now!
I even managed to understand one of Tom Ogilvys posts (thats a first for me)
<vbg

Check out my code to see how I got it to work. I have only posted the spin
button code as there is too much relating to things that work OK (and I am
too ashamed of my messy coding) <g

Regards

Kenny

xxxxxxxxxxxxxxxxxxxxx code starts xxxxxxxxxxxxxxxxxxxxxxxxxxx

Private Sub UserForm_initialize()

'note Spinbutton numbers refer to row numbers

Dim MyEnd As Variant
MyEnd = Range("A65536").End(xlUp).Value
' MyEnd added to stop over scrolling - get runtime error without
' and when cancelled error allowed user into database!
'by finding last row in Col A and adding 3 gives same row number as ID.
SpinButton1.Max = MyEnd + 3
SpinButton1.Min = 4
SpinButton1.Value = 4
End Sub

Private Sub SpinButton1_SpinUp()
'On Error Resume Next

idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With

ans = LogEntry.TextBox2.Value
res = Application.Match(CLng(ans), Range(Range("A4"),
Range("A65536").End(xlUp)), 0)
Range(Range("A4"), Range("A65536").End(xlUp))(res).Activate

IdentRetrieve1



End Sub
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'On Error Resume Next

SpinButton1.Value = TextBox2.Text + 2
End Sub
Private Sub SpinButton1_SpinDown()
'On Error Resume Next

idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With

ans = LogEntry.TextBox2.Value
res = Application.Match(CLng(ans), Range(Range("A4"),
Range("A65536").End(xlUp)), 0)
Range(Range("A4"), Range("A65536").End(xlUp))(res).Activate

IdentRetrieve1

End Sub

Private Sub UserForm_QueryClose _
(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
MsgBox "You can't close the form like that!"
Cancel = True
End If

End Sub

Private Sub MyResetSpinnerEnd()


'note Spinbutton numbers refer to row numbers

Dim MyEnd As Variant
MyEnd = Range("A65536").End(xlUp).Value
' MyEnd added to stop over scrolling - get runtime error without
' and when cancelled error allowed user into database!
'by finding last row in Col A and adding 3 gives same row number as ID.
SpinButton1.Max = MyEnd + 3
SpinButton1.Min = 4
SpinButton1.Value = 4

End Sub

xxxxxxxxxxxxxxxxxxxx code ends xxxxxxxxxxxxxxxxxx



"Bob Phillips" wrote in message
...
Kenny (or whatever the N E stands for<g),

It works good for me (doesn't it always).

Post the full code and a bit of description an d I'll try to help.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"N E Body" wrote in message
...
For some reason the code you gave me returned a number two less than
what
was entered.

The number I entered is an ID number in column A

I presume Excel sees this numberin the TextBox as a Row number as my ID
number (x) is in Row (x-2)

Oddly enough I answered my own question in my last thread - when I added
+2
to your code it returned the value I expected.

Many thanks again

Kenny
and yes N E Body is Kennyatwork, Kennyathome etc of
www.crathornehall.com
fame (you have assisted me many times!!!)

"Bob Phillips" wrote in message
...
Don't understand what you are saying.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"N E Body" wrote in message
...
Thanks Bob

If I type in 40 then click spinup it returns 38??
This is because it is refering to row numbers and 38 is row 41
columnA
value! (it is working because it has advanced one row!)

Is it possible to advance 2 rows down

something like
SpinButton1.Value = TextBox1.Text+2

TIA

Kenny


"Bob Phillips" wrote in message
...
Just add this

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

SpinButton1.Value = TextBox1.Text
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"N E Body" wrote in message
...
Many thanks Tom

I have got it working on my userform apart from 1 problem which
I
cannot
fix.

If I enter a number into TextBox2 then click spinup/down I would
like
the
number in textbox2 to advance/decrease by one.
With my code TextBox2 is populated with a low number then spins
from
that
number!

How do I correct this?

Sample of my code below

Many thanks

Kenny

Private Sub UserForm_initialize()

SpinButton1.Max = 3000
SpinButton1.Min = 4
SpinButton1.Value = 4

End Sub



Private Sub SpinButton1_SpinUp()

idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With

ans = LogEntry.TextBox2.Value
res = Application.Match(CLng(ans), Range("A4:A3000"), 0)
Range("A4:A3000")(res).Activate

IdentRetrieve1

End Sub



Private Sub SpinButton1_SpinDown()

idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With

ans = LogEntry.TextBox2.Value
res = Application.Match(CLng(ans), Range("A4:A3000"), 0)
Range("A4:A3000")(res).Activate

IdentRetrieve1

End Sub






"Tom Ogilvy" wrote in message
...
There is no trick to it.

Use the spinup and spindown events to increment an index into
your
range
and
retrieve the value. Assign the retrieved value to the
textbox.
Set
the
min and max values so the spinbutton will not go outside your
range.

Private Sub SpinButton1_SpinUp()
idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With
End Sub
Private Sub SpinButton1_SpinDown()
idex = SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With
End Sub


Private Sub UserForm_Initialize()
SpinButton1.Max = 20
SpinButton1.Min = 1
SpinButton1.Value = 10
End Sub


The buttons in this case work opposite of the rows (spindown
increases
the
row addressed). However, with increasing values in the cell,
it
works
OK
depending on your perspective. However, if you want to have
spinup
decrease
the row (arrow points in the direction of row change), you can
use

Private Sub SpinButton1_SpinUp()
idex = 21 - SpinButton1.Value
With Worksheets(1)
TextBox2.Text = .Range("A1")(idex)
End With
End Sub

as an example (similar change in SpinDown).


--
Regards,
Tom Ogilvy

"N E Body" wrote in message
...
Hello everyone

Can anyone direct me to a site where I can learn how to code
a
spin
button?
I have not used one before and I have not got a clue where
to
start!

I want to use it in the following manner,

I have an Excel database that has a userform for entering
and
retreiving
data. The form has several fields - one of which is
TextBox2
which
shows
the entry number.
I would like to add a spinner to TextBox2 so I can select
the
previous
or
next entry and show the details in the other fields.

I would be glad of any advice!

Kenny

Win 2000 and Millennium
Excel 97 and 2000