Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Event macro for a task
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Event macro for a task
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Event macro for a task
Thank you ,Ken.It worked well.
"TUNGANA KURMA RAJU" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Event macro for a task
Hi Tungana,
Great! Thanks for the feedback. Ken Johnson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Event macro for a task
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. "Ken Johnson" wrote: Hi Tungana, Great! Thanks for the feedback. Ken Johnson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Event macro for a task
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Event macro for a task
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Event macro for a task
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. "Ken Johnson" wrote: 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Event macro for a task
Hi Tungana,
You're welcome. Thanks for the feedback. Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
event macros - xl 2003 | Excel Worksheet Functions | |||
Can T Get Macro To Run! | New Users to Excel | |||
Clear Cell based on event w/o macro? | Excel Worksheet Functions | |||
Make Alignment options under format cells available as shortcut | Excel Discussion (Misc queries) | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) |