SpinButton (activex) to modify a cell value

[mtheriault2000]

Hello

Now that my range addressing problem as been resolved , I want to code for a spinButton to modify a cell value

a) A cell value is populated by a proposed value ( from an external link)
b) I want to modify this proposed valeu ( cell value), using a SpinButton
c) The SpinButton click to increased or decreased the Cell value by a step define in Range ("QtyMouvementPerClick")

Private Sub SpinButton1_Change()
    Dim Value1 As Double
    Value1 = Sheets("Data_In_Out").Range("iProposed_Qty").Value
    Value1 = Value1 + [COLOR="DeepSkyBlue"](SpinButton1.Value [/COLOR]* Range("QtyMouvementPerClick").Value)
    Sheets("Data_In_Out").Range("iProposed_Qty").Value = Value1
End Sub

Clicking on the SpinButton cause the value on the cell to only go up. The click increment correspond to the value of the Range("QtyMouvementPerClick") value

Any help appreciated

Martin

 

[mtheriault2000]

Re: SpinButton (activex) to modify a cell value ( Resolved)

Hello

I did resolved my problem and i want to share my solution for others

My solution was to force a value to the SinButton.vaule = 10
- After the click, I substract the value of the previous value for SpinButton (10)
- Result is either +1 or -1
- I then modify my cell using Cell + or - the Range("QtyMouvementPerClic").value

Private Sub SpinButton1_Change()
    Dim Value1 As Double
    Dim SpinChange As Integer
    SpinChange = SpinButton1.Value - 10
    Value1 = Sheets("Data_In_Out").Range("iProposed_Qty").Value
    Value1 = Value1 + ((SpinChange) * Range("QtyMouvementPerClick").Value)
    Sheets("Data_In_Out").Range("iProposed_Qty").Value = Value1
    SpinButton1.Value = 10
End Sub

Martin

 

[SyedGangsta]

I have been trying to resolve a similar issue myself but not sure if the code you have here will help.

I have a spin button that out puts its value to a linked cell say cell A4 (this is a record ID number)

I use the the ID number to pull up a record using a lookup table.

The spin button allows me to go through the records up and down 1 at a time, this works.

But since my record set has is long about 1 to 400, I have put in some filter option so, user can filter the main lookup table., this means the IDs that the spinbutton needs to go through are not always the same, i.e depending on filter they may be 1,2,4,7,11,16 etc., all the filtered IDs are part of range called FilteredIDs

Is it possible to increment the values only by the values in the Filtered Range? The current value will always be in the linked cell A4 but I dont want IDs that have been filtered out.

Any advice appreciated.