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