none
Cell to TextBox RRS feed

  • Question

  • Hi, all!

    I'm having some problems with textbox in vba.

    I have a lot of textbox, named like this: 

    GOV_2014_23

    Which GOV_ is the pillar, 2014 is year and 23 is the number of the textbox.

    Now, i have this code, to show or hide the box, by the value of a spefic cell:

    'GOV
    Dim i As Integer
    Dim y As Integer
    'GOVERNANÇA PARA 2012
    For y = 2012 To 2012
        For i = 1 To 4
         'Tabela de valores começa na célula R2
            Select Case Plan1.Cells(i + 1, 18).Value
                'Seleciona as textbox com nome GOV_ + Ano + _ + Valor de R
                Case 1 To 3:
                ActiveSheet.Shapes("GOV_" & y & "_" & i).Visible = True
                Case Else:
                ActiveSheet.Shapes("GOV_" & y & "_" & i).Visible = False
            End Select
        Next i
    Next y

    So, after this:

     ActiveSheet.Shapes("GOV_" & y & "_" & i).Visible = True

    i tried to do this: 

     ActiveSheet.Shapes("GOV_" & y & "_" & i).Title = Range(i+1,19)

    Which i tought, that ill change the text in text box, depending of value of the S cell.

    Like: if GOV_2012_1 is visible depending of  value of R2, i want to text of S2 go to TextBox GOV_2012_1.

    But it didn't worked =/

    Hope i was clear explaining, and sorry all for my english.

    Thanks for everything!

    Monday, September 8, 2014 5:28 PM

Answers

  • Thanks Man!

    It didn't work, but make me think another possibility!

    I wrote this: 

    For y = 2012 To 2012
        For i = 1 To 5
         'Tabela de valores começa na célula R2
            Select Case Plan1.Cells(i + 1, 18).Value
                'Seleciona as textbox com nome GOV_ + Ano + _ + Valor de R
                Case 1 To 3:
                ActiveSheet.Shapes("GOV_" & y & "_" & i).Visible = True
                ActiveSheet.Shapes("GOV_" & y & "_" & i).TextFrame.Characters.Text = Plan1.Cells(i + 1, 19)
                Case Else:
                ActiveSheet.Shapes("GOV_" & y & "_" & i).Visible = False
            End Select
        Next i
    Next y

    And works! 

    Anyway, thanks for all help!

    • Marked as answer by BSBAwq Wednesday, September 10, 2014 2:36 PM
    Wednesday, September 10, 2014 12:44 PM

All replies

  • If you want a dynamic link to the value of cell S2 (etc.), you can use

    ActiveSheet.TextBoxes("GOV_" & y & "_" & i).Formula = "=S" & (i + 1)

    If you want a fixed value, use

    ActiveSheet.TextBoxes("GOV_" & y & "_" & i).Text= Range(i + 1, 19).Value


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, September 8, 2014 6:45 PM
  • Thanks for help, but it didn't worked =/

    I forgot to mention, is to read the text from Sheet 1, and the shapes are in sheet 2.

    I did the adjustments, but it still don't work.

    Looks like this: 

    'GOVERNANÇA PARA 2012
    For y = 2012 To 2012
        For i = 1 To 4
         'Tabela de valores começa na célula R2
            Select Case Plan1.Cells(i + 1, 18).Value
                'Seleciona as textbox com nome GOV_ + Ano + _ + Valor de R
                Case 1 To 3:
                ActiveSheet.Shapes("GOV_" & y & "_" & i).Visible = True
    			ActiveSheet.Shapes("GOV_" & y & "_" & i).Title = Plan1.Cells(i + 1, 19)
                Case Else:
                ActiveSheet.Shapes("GOV_" & y & "_" & i).Visible = False
            End Select
        Next i
    Next y
    When i run the macro, i get no errors, but the textbox don't change.
    Tuesday, September 9, 2014 1:16 PM
  • Try changing

       ActiveSheet.Shapes("GOV_" & y & "_" & i).Title = Plan1.Cells(i + 1, 19)

    to

       ActiveSheet.TextBoxes("GOV_" & y & "_" & i).Text = Plan1.Cells(i + 1, 19)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, September 9, 2014 4:03 PM
  • Thanks Man!

    It didn't work, but make me think another possibility!

    I wrote this: 

    For y = 2012 To 2012
        For i = 1 To 5
         'Tabela de valores começa na célula R2
            Select Case Plan1.Cells(i + 1, 18).Value
                'Seleciona as textbox com nome GOV_ + Ano + _ + Valor de R
                Case 1 To 3:
                ActiveSheet.Shapes("GOV_" & y & "_" & i).Visible = True
                ActiveSheet.Shapes("GOV_" & y & "_" & i).TextFrame.Characters.Text = Plan1.Cells(i + 1, 19)
                Case Else:
                ActiveSheet.Shapes("GOV_" & y & "_" & i).Visible = False
            End Select
        Next i
    Next y

    And works! 

    Anyway, thanks for all help!

    • Marked as answer by BSBAwq Wednesday, September 10, 2014 2:36 PM
    Wednesday, September 10, 2014 12:44 PM