Simple VB Query

[Guest]

Hi All

Ok, I'm new to VB but learning slowly. I have what is probably a simple problem

I'm using a single spinner to increase/decrease the value of my quantity field in my spreadsheet. I want to limit the cells with which the spinner works to a specific range

ie

Range = A1:A1
Clicking in one of the cells in this range allows the spinner to increase/decrease the value in that cel
If in any other cell and the spinner is clicked, nothing happens

My code (for the Up button) currently looks like


Private Sub SpinButton1_Spinup(

ActiveCell.Value = ActiveCell.Value +

End Su


I need to limit this function to the range A1:A10 - I was going to use an 'IF' function, but couldn't complete it

Can anyone help

Thank

Stan

 

[xld]

Stan,

Here is some code

Private Sub SpinButton1_SpinDown()
With ActiveCell
If .Column = 1 And .Row < 11 Then
.Value = .Value + 1
If .Value < 0 Then .Value = 0
End If
End With
End Sub

Private Sub SpinButton1_SpinUp()
With ActiveCell
If .Column = 1 And .Row < 11 Then
.Value = .Value + 1
End If
End With
End Su

 

[Guest]

Thanks xld

That works great. Just to furth it however, how would the code be written if a named range was used instead of the defined cell range?

ie

A1:A10 = "Test

Thanks again

Stan

 

[Dick Kusleika]

Stan

If Not Intersect(ActiveCell, Me.Range("Test")) Is Nothing Then

Intersect will return a range where the activecell and the range named Test
intersect. If they don't, it will return nothing.

When I do stuff like this, I use the Worksheet_SelectionChange event. If
the user is in the proper range, then I enable the spin button. If not, I
disable the spin button. It gives a nice visual representation to the user
of when they can use a particular control.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Thanks xld,

That works great. Just to furth it however, how would the code be written

if a named range was used instead of the defined cell range?

 

[xld]

That would be

Private Sub SpinButton1_SpinDown()
With Range("myRange")
If .Column = 1 And .Row < 11 Then
.Value = .Value + 1
If .Value < 0 Then .Value = 0
End If
End With
End Sub

Private Sub SpinButton1_SpinUp()
With Range("myRange")
If .Column = 1 And .Row < 11 Then
.Value = .Value + 1
End If
End With
End Su