alternative to msgbox

[thephoenix12]

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

 

[anilsolipuram]

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

 

[Andibevan]

Hi ,.

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

Ta

Andi

"thephoenix12" <[email protected]>
wrote in message

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]

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 explai
some parts of it, like the Chr(10) part?

-Stev

 

[Earl Kiosterud]

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.

 

[thephoenix12]

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

 

[anilsolipuram]

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]

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)

 

[thephoenix12]

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

 

[anilsolipuram]

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

 

[thephoenix12]

Basically I want the macro to do the same thing it does now, just do i
for more than one cell. So I want it to find the worksheets on whic
D9 has numbers, then I want it to find the worksheets on which D10 ha
numbers, etc, etc

 

[anilsolipuram]

you have input the range like , d5:d11,c3,a2




Sub MACRO9()
Dim W As Worksheet
Dim range_input, e_range As Range
Dim VAL, sh_skip, temp As Variant
sh_skip = "Summary" 'sheetname to skip
VAL = InputBox("Enter which cell to search")
Set range_input = Range(VAL)
For Each e_range In range_input
temp = temp & e_range.Address & Chr(10)
For Each W In Worksheets
W.Select
If W.Name <> sh_skip Then
If (IsNumeric(Range(e_range.Address).Value) And
Range(e_range.Address).Value <> "") Then
temp = temp & W.Name & Chr(10)

End If
End If
Next
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

 

[thephoenix12]

Yes that is what I wanted. Does e_range represent a single cell in a
range?

There is one final thing I want to do with this. When the macro is
done, lets say we were searching through a range of D911, it prints
out a sheet looking like this:

$D$9
Worksheet 2
Worksheet 5
$D$10
Worksheet 9
Worksheet 16
$D$11
Worksheet 3

Two columns to the left of column D, there are names corresponding to
D9, D10, etc. For example, the name in B9 (same throughout the cells
in all worksheets except for the "summary" worksheet) corresponds to
the results of cells D9, and the name in B10 corresponds to the results
from cells D10. Would it be possible to replace the $D$9 with the name
in cell B9, $D$10 with the name in B10, etc?

 

[thephoenix12]

I see that we need to replace the "e_range.Address" in this:

TEMP = TEMP & E_RANGE.ADDRESS & CHR(10)

I am not sure, however, what to replace it with, to get the names from
column B.

 

[anilsolipuram]

You are correct, e_range represent single cell in a range of cells(cells
address you enter)

Yes temp = temp & e_range.Address & Chr(10) is the line you have to
change


it should be

temp = temp & range(e_range.Address).value & Chr(10)


Sub MACRO9()
Dim W As Worksheet
Dim range_input, e_range As Range
Dim VAL, sh_skip, temp As Variant
sh_skip = "Summary" 'sheetname to skip
VAL = InputBox("Enter which cell to search")
Set range_input = Range(VAL)
For Each e_range In range_input
temp = temp & range(e_range.Address).value & Chr(10)
For Each W In Worksheets
W.Select
If W.Name <> sh_skip Then
If (IsNumeric(Range(e_range.Address).Value) And
Range(e_range.Address).Value <> "") Then
temp = temp & W.Name & Chr(10)

End If
End If
Next
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]

minor error change it should be

temp = temp & range("b" & e_range.row).value & Chr(10

 

[thephoenix12]

Yup that works great! Thanks for all your help anilsolipuram!!

 

[thephoenix12]

Haha, I was just checking over it and I have one more minor request. I
there a simple way to make the names displayed in bold text? Somethin
like

temp = temp & -(insert something to make this bold)- Range("b"
e_range.Row).Value -(insert something to end the bold statement)-
Chr(10

 

[anilsolipuram]

Doesnot work like html

Range("a5").Select
Selection.Font.Bold = True

will change cell a5 to bold


Range("a5").Select
ActiveCell.Characters(Start:=1, Length:=7).Font.FontStyle = "Bold"

will change first 7 letters of the cell a5 to bold

 

[thephoenix12]

Is there a way I can use that to make just the names bold? Or maybe i
I added a symbol or something before each name in the code, and the
had the macro search for that symbol, and when it finds it change th
cell it is in to bold. That is probably really complicated though an
I just need something simple