Geek asks and answers

Is it possible to dynamically control the location of a text field in Excel 2007

I am working on a panel project where I will have three values: minimum value, maximum value and current value. The min and max values ​​will be the endpoints of the bar, and I would like to place the text box containing the current value in the appropriate place along the panel. See below:

Is it possible to do this in Excel, and if so, how can I do it. I have some experience with Visual Basic, but I have not come across this before.

enter image description here

Ultimately, I try to execute the version of Excel in the control panel at the following link:

Link to the dashboard

+5
source share
3 answers

, , . :

Sub SolutionShape(currentVal)

Dim shpBar As Shape, shpCurrent As Shape

'let 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 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

, :

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

, , .

+1

, Shape . . .

, . IncrementTop IncrementLeft. Top Left.

, Shape - ( ), .

, PositionIndicator:

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


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

, Range("CELLADDRESS").Value2

, , :

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

.

+3

, - ( 1), - 2; % complete.

Note. It must be adjusted to offset the part of the form of the text field, which is located to the left of the arrow head.

Option Explicit
Public Sub movebox()

    Dim textbox As Shape, progbar As Shape
    Dim ws As Worksheet
    Dim stp As Integer, endp As Integer
    Dim tbdyn As Integer
    Dim mn As Double, mx As Double, actper As Double, cur As Double
    Dim admn As Double, admx As Double

    Set ws = Sheets("sheet1")
    Set progbar = ws.Shapes(1)
    Set textbox = ws.Shapes(2)

'// Far left of progress bar position
    stp = progbar.Left
'// Far right of progress bar position
    endp = (progbar.Width + stp)

'// Adjust for starting at 0.51
'// You could adjust mn,mx and cur to take the values
'// from the appropriate cells on the spreadsheet
    mn = 0.51
    mx = 6.07
    admn = 0
    admx = 6.07 - mn
    cur = 4
'// Calculate percentage complete
    actper = cur / admx
'// Apply percentage to progress bar
    tbdyn = actper * endp
'// Move the textox appropriately
    textbox.Left = tbdyn

End Sub
+1
source

More articles:


All Articles