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
  #21  
Old June 22nd, 2005, 10:01 PM
anilsolipuram
external usenet poster
 
Posts: n/a
Default


Do you want bold the text in textfile or that in the original excel
file, you cannot bold text in textfile by using macro


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

  #22  
Old June 23rd, 2005, 06:42 PM
thephoenix12
external usenet poster
 
Posts: n/a
Default


I would like to bold the text of the names only (row B) on the new excel
file that is created when the macro is run. Even though the original
text is bold, it is not on the new excel file. Any ideas?


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

  #23  
Old June 23rd, 2005, 07:02 PM
anilsolipuram
external usenet poster
 
Posts: n/a
Default


This will create excel file instead of textfile we previous had, makes
the text bold




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("b" & e_range.Row).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.Font.Bold = True
ActiveCell.Offset(1, 0).Select

Next
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:="C:\sheetname.xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
_
CreateBackup:=False

ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks.OpenText Filename:="C:\sheetname.xls"
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

  #24  
Old June 23rd, 2005, 07:16 PM
thephoenix12
external usenet poster
 
Posts: n/a
Default


That makes everything in the new worksheet bold. Is there a way to make
only the names (obtained from column B, in this part of the code "temp =
temp & Chr(10) & Range("b" & e_range.Row).Value & Chr(10)") bold, on the
new worksheet?


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

  #25  
Old June 23rd, 2005, 07:27 PM
thephoenix12
external usenet poster
 
Posts: n/a
Default


Im thinking of doing something like this: the names are all under 15
characters, everything else is larger, and they all contain no numbers,
as compared to mostly everything else.

So what if we were to insert and If statement saying If cell has no
numbers and has less than 15 characters, Then Selection.Font.Bold =
True End If. As you can see though, I dont know how to do the first
part with the numbers and the characters.


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

  #26  
Old June 23rd, 2005, 09:22 PM
anilsolipuram
external usenet poster
 
Posts: n/a
Default


try this now

Sub MACRO99()
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 & "bold" & Range("b" & e_range.Row).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
MsgBox temp
Workbooks.Add
temp1 = Split(temp, Chr(10))
Range("a1").Select

Dim ch_bold As Variant

For i = 0 To UBound(temp1)
ch_bold = Split(temp1(i), "bold")
If (UBound(ch_bold) 0) Then

Selection.Value = ch_bold(1)

ActiveCell.Font.Bold = True
Else
Selection.Value = temp1(i)
End If
ActiveCell.Offset(1, 0).Select
Next
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:="C:\sheetname.xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks.OpenText Filename:="C:\sheetname.xls"
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

  #27  
Old June 24th, 2005, 07:17 PM
thephoenix12
external usenet poster
 
Posts: n/a
Default


Hey, right now I am trying to clean this macro up so that, on the final
excel output there are no two blank spaces in a row...let me explain;
this is a sample output excel file

Person 1
Project 3
Project 6

Person 2
Project 1



Person 3
Project 7

The extra spaces come in because there is a space on the original excel
file, because of the way it is organized. So when the program outputs
the value of the corresponding cell in column b, it is only outputting
a blank cell. I am trying to write the program so that it skips cells
in column b when there are blank spaces, however I keep getting an
error message. Here is the program with what I have added in bold:

Sub ProjectSearch()
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 range to search in:")
Set range_input = Range(VAL)

For Each e_range In range_input
IF RANGE(\"B\" & E_RANGE.ROW).VALUE.CHARACTERS.COUNT 0 THEN
temp = temp & Chr(10) & Range("b" & e_range.Row).Value & Chr(10)
END IF
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
Range("a1").Select
Selection.Value = "Title"
Selection.Font.Bold = True
temp1 = Split(temp, Chr(10))
Range("a2").Select
For i = 0 To UBound(temp1)
Selection.Value = temp1(i)
If ActiveCell.Characters.Count 13 Then
ActiveCell.Font.Bold = True
End If
ActiveCell.Offset(1, 0).Select
Next
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\srh.HSNPARCH\Desktop\test.txt", _
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks.OpenText Filename:="C:\Documents and
Settings\srh.HSNPARCH\Desktop\test.txt"
End Sub

Do you have any ideas on what I should do?

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

  #28  
Old June 24th, 2005, 07:45 PM
anilsolipuram
external usenet poster
 
Posts: n/a
Default


try with

if trim(Range("b" & e_range.Row).Value)"" Then


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

  #29  
Old June 24th, 2005, 07:45 PM
anilsolipuram
external usenet poster
 
Posts: n/a
Default


try with

if trim(Range("b" & e_range.Row).Value)"" Then


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

  #30  
Old June 27th, 2005, 01:54 PM
thephoenix12
external usenet poster
 
Posts: n/a
Default


Thanks that works


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
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 10:33 PM.


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