Linking a cell to a textbox

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got a userform in which there are two frames (1&2). There is also one
multipage which has 8 pages with spreadsheets in each. I would like to be
able to link a cell (N14 on spreadsheet 1) to Textbox5 (frame2) by using a
VB code, so that when the value in the spreadsheet changes it also changes
the value in the
texbox
 
Put this in the code for Sheet 1 (change any names to fit your situation).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
UserForm1.Textbox5.Value = Sheets("spreadsheet 1").Range("N14").Text
Application.ScreenUpdating = True
End Sub
 
Unfortunately it has'nt worked. I've tried changing names but obviously I'm
doing something wrong! Any other way of tackling this??
 
Hard to say without seeing it. Have you turned screen updating off? Have
you also tried setting the ControlSource for the textbox to reference the
appropriate range?
 
Played around with the code and it's working. But the value in the textbox
only comes visible when I select another cell and when I reopen the programme
the values are not saved........the textbox remains blank until a new value
is placed in the cell. Does that make sence.
 
Many thanks for your prompt reply.
I've ended up putting it in 'UserForm1 code' as:
Private Sub Spreadsheet1_SelectionChange(ByVal EventInfo As OWC.Spreadsheet
EventInfo)
Application.ScreenUpdating= False
UserForm1.TextBox5.Value= Spraedsheet1.Cells(3, 14).Text
Application.ScreenUpdating= True
End Sub

That seems to work to get the value to the textbox. But when I close and
reopen the textbox is blank until I move the selected cell (in the
spreadsheet).
 
Maybe you can get something out of this. Here is a sequence
that creates a textbox, changes it's name and...then the bit
I think you need, sets desired Properties - LinkedCell
is the key.


Public Sub addTextbox()
Sheets("Summary").OLEObjects.Add "Forms.Textbox.1", _
Left:=450, Top:=75, Height:=100, Width:=400
End Sub

Public Sub nameTextbox()
Sheets("Summary").TextBox1.Name = "comment1"
End Sub

Public Sub setTextboxProps()
Sheets("Summary").comment1.Font.Bold = 0
Sheets("Summary").comment1.Font.Italic = 0
Sheets("Summary").comment1.Font.Size = 10
Sheets("Summary").comment1.LinkedCell = "Control!A1"
Sheets("Summary").comment1.EnterKeyBehavior = True
Sheets("Summary").comment1.MultiLine = True
Sheets("Summary").comment1.ScrollBars = 2
Sheets("Summary").comment1.Shadow = True
Sheets("Summary").comment1.TabKeyBehavior = False

End Sub

HTH - Lynn.
 
Many thanks that was ok.
--
KK


Lyndon Rickards said:
Maybe you can get something out of this. Here is a sequence
that creates a textbox, changes it's name and...then the bit
I think you need, sets desired Properties - LinkedCell
is the key.


Public Sub addTextbox()
Sheets("Summary").OLEObjects.Add "Forms.Textbox.1", _
Left:=450, Top:=75, Height:=100, Width:=400
End Sub

Public Sub nameTextbox()
Sheets("Summary").TextBox1.Name = "comment1"
End Sub

Public Sub setTextboxProps()
Sheets("Summary").comment1.Font.Bold = 0
Sheets("Summary").comment1.Font.Italic = 0
Sheets("Summary").comment1.Font.Size = 10
Sheets("Summary").comment1.LinkedCell = "Control!A1"
Sheets("Summary").comment1.EnterKeyBehavior = True
Sheets("Summary").comment1.MultiLine = True
Sheets("Summary").comment1.ScrollBars = 2
Sheets("Summary").comment1.Shadow = True
Sheets("Summary").comment1.TabKeyBehavior = False

End Sub

HTH - Lynn.
Click to expand...
 
Back
Top