Scrollbar changes 2

[clifftech]

Based the resarch I've done I don't know if this is possible but I need a scroll bar that gives me incremental changes of .1 and also allows me manually enter the decimal value.

For example the linked cell to the scrollbar is B11 and I need a range of .1 to 15.0 in increments of .1

I also want to be able to manually enter a value like 5.9 in cell B11

I'm thinking I will need to use VBA code but not sure where to start.

Thanks for the help.

 

[SkipVought]

Hi,

The smallest incriment in a control is ONE.

But all is not lost If YOUR range is .1 to 15 in icriments of .1 then make the CONTROLS limits and incriment 1 to 150 in incriments of 1, and convert accordingly.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[clifftech]

I agree but I need the cell to show the converted amount (in decimals) in cell B11 and still allow me to enter values in B11 manually.

 

[SkipVought]

So???

YOU enter 5.9 in B11.

Let's say the linked cell is C11, that contains =B11*10

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[clifftech]

I guess I'm not very clear with my problem or I don't understand your response.

B11 is used in a formula involving other cells. I need to view the value in cell B11 and also want to change the value of B11 using a scroll bar (which is linked to B11) in increments of .1 and also need to manually enter values in B11.

Thanks for trying to help.

 

[SkipVought]

Cannot do what you want to do without VBA code.

Copy this code to the SHEET CODE Window (right-click the sheet tab and select View Code)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [B11]) Is Nothing Then _
     ActiveSheet.Shapes("Scroll Bar 1").OLEFormat.Object.Value = [B11] * 10
End Sub

Copy this code to Module1 - (alt+F11 Insert > Module)

Sub ScrollBar1_Change()
    [B11] = ActiveSheet.Shapes("Scroll Bar 1").OLEFormat.Object.Value / 10
End Sub

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[clifftech]

Two problems are occuring.

I added the VBA code as instructed but the increment changes are in still in increments of 1 when I use the scrollbar.

When I try to manually enter a value in B11 I get the error message: "The index into the specified collection is out of bounds." and when I click on debug the problem code line is in the sheet code line "ActiveShee.Shapes..."

BTW - I'm using Office 2003

 

[SkipVought]

Sorry, missed one step.

Right click the control and select Assign Macro and hit the New button.

This is where this code belongs...

Sub ScrollBar1_Change()
    [B11] = ActiveSheet.Shapes("Scroll Bar 1").OLEFormat.Object.Value / 10
End Sub

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[clifftech]

We are getting close. When the value is divided by 10 it is truncating to a whole digit. For example, I scroll to 172 and the value will be 1.00. Same with the manual entry, if I enter 172 it will show as 17.00

 

[SkipVought]

your scrollbar limits should be 1 and 150

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[clifftech]

I have the limits at 1 and 150. The value that shows up in cell B11 is truncated to a whole number - nothing to the right of the decimal. I thought it might be a format issue and changed the format but the problem remains.

 

[SkipVought]

What is the value in the Formula Bar, when you select B11?

I get values to the tenth.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[clifftech]

Got it. I had entered a value in the Control form for the cell link. After I eliminated the cell link it work fine.

Thanks much for your help. Could not have figured this one out on my own.

 

[Welshbird]

Clifftech - I suspect you just missed awarding a star to Skip for his help here - so I'll do it on your behalf.

Thanks,

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[clifftech]

I agree. Thanks.