Event macro for a task

  • Thread starter TUNGANA KURMA RAJU
  • Start date
T

TUNGANA KURMA RAJU

Whenever I open that workbook ,datavalues of sheet 2, F2:G6 be populated in
msg.box once only and after acknowledging with ok button macro should end.
 
K

Ken Johnson

Hi Tungana,
try this pasted into ThisWorkbook code module...

Private Sub Workbook_Open()
Dim rngInfo As Variant
Dim stInfo As String
Dim I As Byte
Dim J As Byte
rngInfo = Sheet2.Range("F2:G6")
For I = 1 To 5
For J = 1 To 2
stInfo = stInfo & rngInfo(I, J) & IIf(J = 1, ", ", "")
Next J
If I <> 5 Then Let stInfo = stInfo & Chr(10)
Next I
MsgBox Prompt:=stInfo, Title:="Values in Sheet2 F2:G6"
End Sub

Ken Johnson
 
T

TUNGANA KURMA RAJU

Hi,Ken ,
Just a cosmetic change I want to add.My range as I mentioned in my question
F2:G6 ,is maximum range.sometimes it may contain only 1 row(2 records) i.e
F2:G2 sometimes 4 records,F2:G3,so mymessage box looks ugly when it has one
row.Modification of code to this affect possible? Then please do it.I shall
be thankful.
 
K

Ken Johnson

Hi Tungana,
Try this.
I have also included a line that prevents the message box from being
displayed should all the cells in F2:G6 be empty. If you really still
want the msgbox even when there are no values to show then just delete
(or type an ' in front of ) the line that goes "If iRows = 0 Then Exit
Sub"
I've also assumed for each of the 5 rows that if there is a value in
column F then there is also a value in column G and conversely if no
value in column F then no value in column G.

Private Sub Workbook_Open()
Dim rngInfo As Variant
Dim stInfo As String
Dim I As Byte
Dim J As Byte
Dim iRows As Byte
rngInfo = Sheet2.Range("F2:G6")
'count rows with values
For I = 1 To 5
If rngInfo(I, 1) <> "" Then Let iRows = iRows + 1
Next I
'if no values then don't show MsgBox
If iRows = 0 Then Exit Sub
For I = 1 To iRows
For J = 1 To 2
stInfo = stInfo & rngInfo(I, J) & IIf(J = 1, ", ", "")
Next J
If I <> iRows Then Let stInfo = stInfo & Chr(10)
Next I
MsgBox Prompt:=stInfo, Title:="Values in Sheet2 F2:G6"
End Sub

Ken Johnson
 
K

Ken Johnson

Hi Tungana,
I left out one other assumption that I have made, namely that the range
F2:G6 has no gaps. By that I mean when new values are added they are
entered into the next available row, now rows are skipped.
Ken Johnson
 
T

TUNGANA KURMA RAJU

A million thanks ,Mr.Ken,now my message looks great and beautiful.you have
done a great job.Don't worry about your last assumption,as my range F2:G6 is
based on a formula ,there will be no gaps in rows, and all new values are
added into next available rows only.Thanks once again for helping me.
 
Top