Textbox on excel sheet do not update with VBA

  • Thread starter Thread starter Ben Joiner
  • Start date Start date
B

Ben Joiner

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
 
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.
 
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
 
Hi Ben,
Probably a problem with a loop. No one can suggest a fix till they see
the problem...
regards
Paul
 
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
==============================================
 
Back
Top