Is it possible to dynamically control the location of a textbox in excel 2007

Question

I working on a dashboard project where I will have three values: min value, max value and current value. The min and max values will be the end-points of a bar and I'd like to place a text box containing the current value at the appropriate location along the bar. See below:

Is it possible to do this in Excel and if so, how would I go about achieving this. I have some experience with Visual Basic, but I haven't come across this one before.

Ultimately, I am attempting to do an excel version of the dashboard at the following link:

Link to Dashboard

Answer 1

Turn on the macro recording when the Shape-object is not selected. Now select it and change its position. Stop recording and use the code generated.

It looks useful to me when I tried it. I got some IncrementTop and IncrementLeft code. You can also use the Top and Left property directly.

It might be an idea to change the name of the Shape-object into something meaningful (in the address box left of the formula box) so your code gets more readable.

So for my Shape named PositionIndicator:

ActiveSheet.Shapes("PositionIndicator").Left = 250

Or

ActiveSheet.Shapes("PositionIndicator").Left = _ 
    ActiveSheet.Shapes("PositionIndicator").Left + 5

To link it to a cell value just use Range("CELLADDRESS").Value2

To apply it every time you change the cells values use:

Private Sub Worksheet_Change(ByVal Target As Range)
    'Here your script to check if the change concerns one of your input cells and then run the code to change the location of the Shape-object
End Sub

Good luck

Answer 2

I like your idea therefore I checked how could the complete code looks like. Here is the result:

Sub SolutionShape(currentVal)

Dim shpBar As Shape, shpCurrent As Shape

'let's assume we have only two shapes on the Activesheet
Set shpBar = ActiveSheet.Shapes(1)
Set shpCurrent = ActiveSheet.Shapes(2)

Dim barMin As Double, barMax As Double
    barMin = 0.51              'both values could be taken from sheet
    barMax = 6.75

'let's do it visualy complicated this time :)
With shpCurrent
    .Left = (-.Width / 2 + shpBar.Left) + _
        (((currentVal - barMin) / (barMax - barMin)) * shpBar.Width)

    **'EDITED- adding information about current value:**
    .TextFrame.Characters.Text = currentVal
End With

End Sub

Call the procedure from event of from immediate window for test, eg.:

SolutionShape 0.51      'go to beginning
SolutionShape 6.75      'go to end

This solution will work wherever you place shapes and whatever new dimensions of them you set.