| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Textbox on excel sheet do not update with VBA
I have a problem at present in that when I run some
simple code to update 14 textboxs on a worksheet, with variables extracted from another worksheet (Datasheet). Once the variables have been entered into the relevant textboxes. The sheet is then printed. The variable are extracted line by line on another worksheet, entered into the textboxes on another sheet and then printed. The next set of variables are then extracted from the next line down (Data sheet) and then entered into the relevant textboxes, printed, and so on and so on. However, the textboxes never update after the first line of data has been completed. Therefore I end up with 30 print outs containing exactly the same data. Any help would be appreciatted. egards Ben Joiner |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Textbox on excel sheet do not update with VBA
Hard to say where the problem might be without seeing your code, but try
putting in DoEvents Application.ScreenUpdating = False Application.ScreenUpdating = True right after you update the textboxes and before you print. -- Regards, Tom Ogilvy "Ben Joiner" wrote in message ... I have a problem at present in that when I run some simple code to update 14 textboxs on a worksheet, with variables extracted from another worksheet (Datasheet). Once the variables have been entered into the relevant textboxes. The sheet is then printed. The variable are extracted line by line on another worksheet, entered into the textboxes on another sheet and then printed. The next set of variables are then extracted from the next line down (Data sheet) and then entered into the relevant textboxes, printed, and so on and so on. However, the textboxes never update after the first line of data has been completed. Therefore I end up with 30 print outs containing exactly the same data. Any help would be appreciatted. egards Ben Joiner |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Textbox on excel sheet do not update with VBA
Thanks for the response Tom.
I have attached the code below: As I say it updates the first time, and then the values stay the same for each line processed. When the program finishes, the newvalues then appear in the relevant textboxes. Thanks Again Ben Sub print_mpds() Application.DisplayAlerts = False Workbooks.Open Filename:="G:\london operations\international\daily data\international trade file.xls" Application.DisplayAlerts = True Sheets("new bargains").Select Let bargcount = Application.WorksheetFunction.CountA(Range ("a:a")) - 2 For z = 2 To bargcount Sheets("new bargains").Select If Cells(z, 2) = "B" Then Let boughtsold = "BOUGHT" Else Let boughtsold = "SOLD" End If Let curr = Cells(z, 3) Let bargref = Cells(z, 1) Let client = Cells(z, 4) If Cells(z, 5) = "P" Then Let nomind = "PAPER" Else Let nomind = Cells(z, 5) End If Let w8ben = "NAP" Let prc = Cells(z, 16) Let branch = "NAP" Let contact = "Ben Joiner" Let sedol = Cells(z, 6) Let stkname = Cells(z, 7) Let qty = Cells(z, 8) Let traddate = Cells(z, 11) Let setdate = Cells(z, 12) Let consid = Cells(z, 9) Let cpty = Cells(z, 10) If Right(Cells(z, 10), 1) = "B" And Left(Cells(z, 10), 1) < 10 Then Let bond = "BOND" Else Let bond = "Equity" End If Sheets("MPD").Select Sheets("MPD").textbox1.Text = boughtsold Sheets("MPD").textbox2.Text = curr Sheets("MPD").textbox3.Text = bargref Sheets("MPD").textbox4.Text = client Sheets("MPD").textbox5.Text = nomind Sheets("MPD").textbox6.Text = w8ben Sheets("MPD").textbox7.Text = prc Sheets("MPD").textbox8.Text = branch Sheets("MPD").textbox9.Text = contact Sheets("MPD").textbox10.Text = sedol Sheets("MPD").textbox11.Text = stkname Sheets("MPD").textbox12.Text = qty Sheets("MPD").textbox13.Text = traddate Sheets("MPD").textbox14.Text = setdate Sheets("MPD").textbox15.Text = consid Sheets("MPD").textbox16.Text = cpty Sheets("mpd").textbox17.Text = bond Sheets("MPD").Select Cells(1, 1).Select ActiveCell.Formula = z - 1 Application.ScreenUpdating = False Application.ScreenUpdating = True ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Next z End Sub -----Original Message----- Hard to say where the problem might be without seeing your code, but try putting in DoEvents Application.ScreenUpdating = False Application.ScreenUpdating = True right after you update the textboxes and before you print. -- Regards, Tom Ogilvy "Ben Joiner" wrote in message ... I have a problem at present in that when I run some simple code to update 14 textboxs on a worksheet, with variables extracted from another worksheet (Datasheet). Once the variables have been entered into the relevant textboxes. The sheet is then printed. The variable are extracted line by line on another worksheet, entered into the textboxes on another sheet and then printed. The next set of variables are then extracted from the next line down (Data sheet) and then entered into the relevant textboxes, printed, and so on and so on. However, the textboxes never update after the first line of data has been completed. Therefore I end up with 30 print outs containing exactly the same data. Any help would be appreciatted. egards Ben Joiner . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Textbox on excel sheet do not update with VBA
Hi Ben,
Probably a problem with a loop. No one can suggest a fix till they see the problem... regards Paul "Ben Joiner" wrote in message ... I have a problem at present in that when I run some simple code to update 14 textboxs on a worksheet, with variables extracted from another worksheet (Datasheet). Once the variables have been entered into the relevant textboxes. The sheet is then printed. The variable are extracted line by line on another worksheet, entered into the textboxes on another sheet and then printed. The next set of variables are then extracted from the next line down (Data sheet) and then entered into the relevant textboxes, printed, and so on and so on. However, the textboxes never update after the first line of data has been completed. Therefore I end up with 30 print outs containing exactly the same data. Any help would be appreciatted. egards Ben Joiner |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Textbox on excel sheet do not update with VBA
I think your code is suspect. These versions work for me (XL97) :-
ActiveSheet.TextBoxes("Text Box 1").Text = "xxxx" ActiveSheet.TextBoxes(1).Text = "xxxx" Regards BrianB ============================================== wrote in message ... Thanks for the response Tom. I have attached the code below: As I say it updates the first time, and then the values stay the same for each line processed. When the program finishes, the newvalues then appear in the relevant textboxes. Thanks Again Ben Sub print_mpds() Application.DisplayAlerts = False Workbooks.Open Filename:="G:\london operations\international\daily data\international trade file.xls" Application.DisplayAlerts = True Sheets("new bargains").Select Let bargcount = Application.WorksheetFunction.CountA(Range ("a:a")) - 2 For z = 2 To bargcount Sheets("new bargains").Select If Cells(z, 2) = "B" Then Let boughtsold = "BOUGHT" Else Let boughtsold = "SOLD" End If Let curr = Cells(z, 3) Let bargref = Cells(z, 1) Let client = Cells(z, 4) If Cells(z, 5) = "P" Then Let nomind = "PAPER" Else Let nomind = Cells(z, 5) End If Let w8ben = "NAP" Let prc = Cells(z, 16) Let branch = "NAP" Let contact = "Ben Joiner" Let sedol = Cells(z, 6) Let stkname = Cells(z, 7) Let qty = Cells(z, 8) Let traddate = Cells(z, 11) Let setdate = Cells(z, 12) Let consid = Cells(z, 9) Let cpty = Cells(z, 10) If Right(Cells(z, 10), 1) = "B" And Left(Cells(z, 10), 1) < 10 Then Let bond = "BOND" Else Let bond = "Equity" End If Sheets("MPD").Select Sheets("MPD").textbox1.Text = boughtsold Sheets("MPD").textbox2.Text = curr Sheets("MPD").textbox3.Text = bargref Sheets("MPD").textbox4.Text = client Sheets("MPD").textbox5.Text = nomind Sheets("MPD").textbox6.Text = w8ben Sheets("MPD").textbox7.Text = prc Sheets("MPD").textbox8.Text = branch Sheets("MPD").textbox9.Text = contact Sheets("MPD").textbox10.Text = sedol Sheets("MPD").textbox11.Text = stkname Sheets("MPD").textbox12.Text = qty Sheets("MPD").textbox13.Text = traddate Sheets("MPD").textbox14.Text = setdate Sheets("MPD").textbox15.Text = consid Sheets("MPD").textbox16.Text = cpty Sheets("mpd").textbox17.Text = bond Sheets("MPD").Select Cells(1, 1).Select ActiveCell.Formula = z - 1 Application.ScreenUpdating = False Application.ScreenUpdating = True ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Next z End Sub -----Original Message----- Hard to say where the problem might be without seeing your code, but try putting in DoEvents Application.ScreenUpdating = False Application.ScreenUpdating = True right after you update the textboxes and before you print. -- Regards, Tom Ogilvy "Ben Joiner" wrote in message ... I have a problem at present in that when I run some simple code to update 14 textboxs on a worksheet, with variables extracted from another worksheet (Datasheet). Once the variables have been entered into the relevant textboxes. The sheet is then printed. The variable are extracted line by line on another worksheet, entered into the textboxes on another sheet and then printed. The next set of variables are then extracted from the next line down (Data sheet) and then entered into the relevant textboxes, printed, and so on and so on. However, the textboxes never update after the first line of data has been completed. Therefore I end up with 30 print outs containing exactly the same data. Any help would be appreciatted. egards Ben Joiner . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Automaticaly update a textbox from filtered cells | Excel Discussion (Misc queries) | |||
| Textbox changes position on a copied excel-sheet | Excel Discussion (Misc queries) | |||
| new line at sheet textbox | Excel Programming | |||
| Accessing a textbox on a sheet.... | Excel Programming | |||
| Textbox object does not update | Excel Programming | |||
Linear Mode
