Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking a cell to a textbox
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 -- KK |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking a cell to a textbox
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 "George" wrote: 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 -- KK |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking a cell to a textbox
Unfortunately it has'nt worked. I've tried changing names but obviously I'm
doing something wrong! Any other way of tackling this?? -- KK "JNW" wrote: 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 "George" wrote: 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 -- KK |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking a cell to a textbox
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? "George" wrote: Unfortunately it has'nt worked. I've tried changing names but obviously I'm doing something wrong! Any other way of tackling this?? -- KK "JNW" wrote: 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 "George" wrote: 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 -- KK |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking a cell to a textbox
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. -- KK "George" wrote: Unfortunately it has'nt worked. I've tried changing names but obviously I'm doing something wrong! Any other way of tackling this?? -- KK "JNW" wrote: 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 "George" wrote: 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 -- KK |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking a cell to a textbox
Again, we'll need to see some code in order to answer properly.
"George" wrote: 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. -- KK "George" wrote: Unfortunately it has'nt worked. I've tried changing names but obviously I'm doing something wrong! Any other way of tackling this?? -- KK "JNW" wrote: 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 "George" wrote: 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 -- KK |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking a cell to a textbox
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). -- KK "JNW" wrote: Again, we'll need to see some code in order to answer properly. "George" wrote: 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. -- KK "George" wrote: Unfortunately it has'nt worked. I've tried changing names but obviously I'm doing something wrong! Any other way of tackling this?? -- KK "JNW" wrote: 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 "George" wrote: 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 -- KK |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking a cell to a textbox
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. George wrote: 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). |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking a cell to a textbox
Many thanks that was ok.
-- KK "Lyndon Rickards" wrote: 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. George wrote: 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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking a textbox to a cell in another workbk | Excel Discussion (Misc queries) | |||
Linking a textbox to a cell in another workbk | Excel Discussion (Misc queries) | |||
Linking Textbox and cell | Excel Programming | |||
Linking textbox and graphs | Excel Discussion (Misc queries) | |||
HELP! I Lost The Ability To Advance From TextBox To TextBox With the ENTER Or The TAB Keys | Excel Programming |