Linking a cell to a textbox

[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

 

[Guest]

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

 

[Guest]

Unfortunately it has'nt worked. I've tried changing names but obviously I'm
doing something wrong! Any other way of tackling this??

 

[Guest]

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?

 

[Guest]

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.

 

[Guest]

Again, we'll need to see some code in order to answer properly.

 

[Guest]

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).

 

[Lyndon Rickards]

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.

 

[Guest]

Many thanks that was ok.
--
KK

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.