A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

alternative to msgbox



 
 
Thread Tools Display Modes
  #1  
Old June 22nd, 2005, 02:26 PM
thephoenix12
external usenet poster
 
Posts: n/a
Default alternative to msgbox


Hi,

I have a macro that searches for something in multiple worksheets, and
each time it finds what it is looking for, a message box pops
displaying the name of the worksheet. Instead of this, I would like it
to display all the worksheet names on a notepad sheet, or word file, or
something that is printable.

Thanks for your help,

-Steve


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #2  
Old June 22nd, 2005, 03:17 PM
anilsolipuram
external usenet poster
 
Posts: n/a
Default


This would save the worksheet names to c:\worksheet_names.txt

Sub MACRO9()
Dim W As Worksheet
Dim VAL, sh_skip, temp As Variant
sh_skip = "Summary" 'sheetname to skip
VAL = InputBox("Enter which cell to search")
For Each W In Worksheets
W.Select
If W.Name sh_skip Then
If (IsNumeric(Range(VAL).Value) And Range(VAL).Value "")
Then
temp = temp & W.Name & Chr(10)
End If
End If
Next
Workbooks.Add
temp1 = Split(temp, Chr(10))
Range("a1").Select
For i = 0 To UBound(temp1)
Selection.Value = temp1(i)
ActiveCell.Offset(1, 0).Select
Next
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"C:\worksheet_names.txt", _
FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #3  
Old June 22nd, 2005, 03:31 PM
Andibevan
external usenet poster
 
Posts: n/a
Default

Hi ,.

If you post your existing code (would suggest you use the excel.programming
forum) it would help.

Ta

Andi

"thephoenix12"
wrote in message
news:thephoenix12.1r16uq_1119449139.1649@excelforu m-nospam.com...

Hi,

I have a macro that searches for something in multiple worksheets, and
each time it finds what it is looking for, a message box pops
displaying the name of the worksheet. Instead of this, I would like it
to display all the worksheet names on a notepad sheet, or word file, or
something that is printable.

Thanks for your help,

-Steve


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile:
http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213


  #4  
Old June 22nd, 2005, 03:38 PM
thephoenix12
external usenet poster
 
Posts: n/a
Default


Thanks anilsolipuram,

Is there a way to have the file open when the macro is done? And also,
if it is not too much trouble, can you go through the code and explain
some parts of it, like the Chr(10) part?

-Steve


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #5  
Old June 22nd, 2005, 03:40 PM
Earl Kiosterud
external usenet poster
 
Posts: n/a
Default

Steve,

This will populate a sheet called "Hits" with your list, to give you the
idea of a possible solution. Create the sheet first. Variable SheetName is
the name your code has found.

Dim Index as long
Index = 1

At the place in your code where the message box is,

Sheets("Hits").Cells(Index, 1) = SheetName
Index = Index + 1

Untested.

--
Earl Kiosterud
www.smokeylake.com/
-------------------------------------------

"thephoenix12"
wrote in message
news:thephoenix12.1r16uq_1119449139.1649@excelforu m-nospam.com...

Hi,

I have a macro that searches for something in multiple worksheets, and
each time it finds what it is looking for, a message box pops
displaying the name of the worksheet. Instead of this, I would like it
to display all the worksheet names on a notepad sheet, or word file, or
something that is printable.

Thanks for your help,

-Steve


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile:
http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213



  #6  
Old June 22nd, 2005, 04:30 PM
thephoenix12
external usenet poster
 
Posts: n/a
Default


The code that anilsolipuram wrote in the first reply is my existing code
plus what he added to put the results in a text file. (He was very
helpful and wrote my existing code yesterday). Right now I am just
wondering if there is a simple way to have the text file open when the
macro is done; and I would also like to know how the newly written code
(the part that writes the results to a text file) works.

Eventually I am going to want the code to search through a range of
cells, not just one cell. For example D930, and have the results
displayed in the text file (with a space or something to separate each
cells results) So instead of entering a single cell for VAL, I am
going to want to enter a range of cells, then have the macro perform
its search for each of those cells. I am trying to figure this out
now, but am not having much luck. If anyone has any suggestions I
would be very grateful.

Thanks,

-Steve


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #7  
Old June 22nd, 2005, 05:48 PM
anilsolipuram
external usenet poster
 
Posts: n/a
Default


This will open the text file in the excel file itself, if you want we
can automate the printing part also

Sub MACRO9()
Dim W As Worksheet
Dim VAL, sh_skip, temp As Variant
sh_skip = "Summary" 'sheetname to skip
VAL = InputBox("Enter which cell to search")
For Each W In Worksheets
W.Select
If W.Name sh_skip Then
If (IsNumeric(Range(VAL).Value) And Range(VAL).Value "")
Then
temp = temp & W.Name & Chr(10)
End If
End If
Next
Workbooks.Add
temp1 = Split(temp, Chr(10))
Range("a1").Select
For i = 0 To UBound(temp1)
Selection.Value = temp1(i)
ActiveCell.Offset(1, 0).Select
Next
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"C:\worksheet_name.txt", _
FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks.OpenText Filename:="C:\worksheet_name.txt"

End Sub


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #8  
Old June 22nd, 2005, 05:54 PM
anilsolipuram
external usenet poster
 
Posts: n/a
Default


Explanation to my previous post

temp = temp & W.Name & Chr(10) (1)
'code
'code

Workbooks.Add (2)
temp1 = Split(temp, Chr(10)) (3)
Range("a1").Select (4)
For i = 0 To UBound(temp1) (5)
Selection.Value = temp1(i) (6)
ActiveCell.Offset(1, 0).Select (7)
Next
Application.DisplayAlerts = False (8)

ActiveWorkbook.SaveAs Filename:= _
"C:\worksheet_name.txt", _
FileFormat:=xlText, CreateBackup:=False (9)
ActiveWorkbook.Save
ActiveWorkbook.Close(10)
Application.DisplayAlerts =false (11)
Workbooks.OpenText Filename:="C:\worksheet_name.txt" (12)

(1) temp variable stores all the worksheet names with chr(10) , new
line charecter between sheet names
(2) add new workbook
(3)split the temp variable to get individual sheet names
(4) select a1 cell in new workbook
(5)(6)(7) loop through all the worksheet variables and put the variable
in column a of new workbook
(8) disable alerts
(9) save the new workbook created as text file in c:\
(10)(11) save and close the new workbook
(12) open the text file in excel.
(1)


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #9  
Old June 22nd, 2005, 06:38 PM
thephoenix12
external usenet poster
 
Posts: n/a
Default


Thanks anilsolipuram, that works perfectly. Now im trying to change it
so it works through a range of cells; basically the same as a user
running it for D9, then D10, then D11, etc. etc., except it would do it
all at once.
It seems to me that I can leave this line:
*"VAL = InputBox("enter which cell to search")*
the same...just have a user input a range instead. I'm then trying to
add a For statement after
*"If W.Name sh_skip Then" *
and then change the next line
*"IF (IsNumeric(Range(VAL).Value) And Range(VAL).Value "") Then" *
so it searches a single cell in the range. I am not having much luck
though, and any help would be appreciated.

Thanks,

-Steve


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213

  #10  
Old June 22nd, 2005, 07:15 PM
anilsolipuram
external usenet poster
 
Posts: n/a
Default


if you want for cells say d9,d10,d11 , you want all the cells to be
numeric or any one of them should be numeric , what is the criteria for
multiple cells.


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=381213

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Access Mail Merge to Word.doc files ? RNUSZ@OKDPS Setting Up & Running Reports 1 May 18th, 2005 06:31 PM
Need Help In Printing Current Record in Specific Report RNUSZ@OKDPS Setting Up & Running Reports 1 May 16th, 2005 09:06 PM
Open & Read Newly created Table for Reporting ? RNUSZ@OKDPS Setting Up & Running Reports 0 May 9th, 2005 03:43 PM
Sorry guyz.... jim0861 Powerpoint 3 April 15th, 2005 03:39 PM
MsgBox Displays Too Early gdtatuiowa General Discussion 7 October 1st, 2004 10:06 PM


All times are GMT +1. The time now is 07:06 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.