Format a Comment created by a Macro

Thanks to Ken Johnson's help I have a Comment box which appears only if a 
condition is met. This comment has been created by a small macro. It seems 
that because the comment box is not a standard Excel comment, but has been 
created by the macro, any attempt to format the text box (dimensions, 
alignment, no shadow etc) fails.  It looks like the formatting has to be 
included in the macro, but so far I haven't been able to do this. Can anyone 
help?
0
KenG (52)
8/30/2005 12:13:01 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
317 Views

Similar Articles

[PageSpeed] 15

Ken

can you post the code that creates the Comment box


-- 
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2473
View this thread: http://www.excelforum.com/showthread.php?threadid=400311

0
8/30/2005 2:54:15 AM
mudraker,
If you are addressing Ken Johnson here is the code I posted for Ken.G.
If you're addressing Ken.G then please ignore this reply.

Just as a demo, if the value in cell A1 of the active sheet changes to
become greater than 10 then Cell B1 has the comment "A1 is greater than

10" added.
The code must be pasted into the ThisWorkbook module. If you don't
want  all sheets affected then paste the code into the WorkSheet_Change

Sub of the sheet you want affected.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If ActiveSheet.Cells(1, 1).Value > 10 Then
With ActiveSheet.Cells(1, 2)
        On Error Resume Next 'if no comment to delete then error is
ignored
        .Comment.Delete 'remove old comment
        On Error GoTo 0
        .AddComment
        .Comment.Visible =3D True 'change to False if you only want to
see the comment
                                'when the cursor is positioned over the

commented cell
        .Comment.Text Text:=3D"A1 is greater than 10"
        .Comment.Shape.TextFrame.AutoS=ADize =3D True 'comment frame size
will suit any size comment
End With
Else: On Error Resume Next
ActiveSheet.Cells(1, 2).Comment.Delete 'delete old comment because A1
is not >10=20
On Error GoTo 0=20
End If=20
End Sub=20

Ken Johnson

0
hanjohn (6)
8/30/2005 3:24:51 AM
Don't ignore Ken Johnson's post. That's the code I used to create the 
conditional comment. I've since tried recording a macro while creating and 
formatting a comment, then copied that code into my macro but it keeps coming 
up with errors. I don't know enough about vbasic I'm afraid.

"mudraker" wrote:

> 
> Ken
> 
> can you post the code that creates the Comment box
> 
> 
> -- 
> mudraker
> ------------------------------------------------------------------------
> mudraker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2473
> View this thread: http://www.excelforum.com/showthread.php?threadid=400311
> 
> 
0
KenG (52)
8/30/2005 6:28:22 AM
Reply:

Similar Artilces:

Creating a group of cells. Need Help Please.
Havn't used excel in a while and I need to create a group of cell corresponding to an input of a min and a max. Here are the details. On one sheet I have a box where you enter th min and a box where you enter the max. In another sheet I want column starting at A2 to output (MIN,A2+1000,A3+1000,....MAX) ho would I do this -- Thundersix ----------------------------------------------------------------------- Thundersixx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3055 View this thread: http://www.excelforum.com/showthread.php?threadid=50207 Name the...

Date format issue when submitting from a userform to a spreadsheet
Hi, I have a userform that I've generated which routes dates onto a spreadsheet based on the users input. I am having a bit of a frustrating time with the dates, it would appear that in the process of moving the date from the userform to the spreadsheet some dates are switched/transposed. I'll give an example. If someone enters 09/02/2004 on the userform excel seems to look at 09 and assume it is a month and transposes the dates to 02/09/2004(this does not appear to be a US/UK format issues as I have already gone down that road). However, if the date 13/09/2004 is entered i...

Macros for worksheet copy & paste?
I have 6 different excel files. How can I combine all 6 of them in one single excel file under individual worksheets (E.g. Worksheet1 for File1, worksheet2 for File2 ....etc)? I do not wish to copy & paste it manually. Can I use a macro to take care of it? If so, where can I find further info on how to go about it? THANKS!! Assuming (1) this is a one-time need, and (2) your workbooks have only one sheet each, this would probably be faster done by hand: 1) With all the files open choose one of the files to be the receiving file. 2) Select the sheet in the subsequent books, one at...

How do I send a welcome message to a new created mailbox automatically?
My exchnage send a message automatically but I need to change the message where do I change it? Thanks, Ron On Tue, 21 Jun 2005 16:35:53 -0700, "Ron" <rony@geotestinc.com> wrote: >My exchnage send a message automatically but I need to change the message >where do I change it? > >Thanks, >Ron > Exchange doesnt do that unless you have something scripted. If you are referring to the welcome message in Outlook, its called welcome.msg , but I dont think its been used since Outlook 2002. So where would I plug in the script? Also I searched for welcome.ms...

Creating a template using Publisher
I need help creating a neighborhood directory template using Publisher and merging an Excel spreadsheet into it. It keeps printing multiple pages of the first page after I merge. What version Publisher? If you have 2003 use the catalog merge for your directory. http://office.microsoft.com/en-us/assistance/CH010504381033.aspx Otherwise you need to setup your page as though it is labels. Determine the size of the area you need for each entry, select labels in page setup, type the size you determined in the page setup. Adjust the gaps and margins when you are ready to print (2000 and bel...

Custom cell formatting
I need to create a custom format for a series of cells that will begin like this. I can't figure out what the code character is fora volitile potentially alpha character. Can anyone help me?? -Monica, Dallas 000000 000001 000002 .... 000009 00000A 00000B 000010 000011 ...... Monica something like this might work for you but you'll need to put all the leading digits in for the entry with the alpha character Regards Trevor "MDavison" <davison@fr.com> wrote in message news:#SD0tUzTEHA.1652@TK2MSFTNGP09.phx.gbl... > I need to create a custom format for a series ...

Auto formatting features: How do I align page numbers in publicati
I've got a problem with my publication. I can't align even page numbers to the left without automatic moving the odd numbers to the left as well? Can someone help me? Cissy99 wrote: > I've got a problem with my publication. I can't align even page numbers to > the left without automatic moving the odd numbers to the left as well? Can > someone help me? You need to create a two-page master rather than a one-page master. What version of Publisher are you running? -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org ...

how to turn off automatic format in Excel?
Hi Excel automatically change the first character in a cell to be uppercase. I just want lowercase. How can I turn off this function? Thanks Ngoc Hi Go to Tools / Autocorrect and uncheck Capitalize first letter of sentence. Andy. "ngoc" <linh@chello.no> wrote in message news:BOKNb.271$O41.819@amstwist00... > Hi > Excel automatically change the first character in a cell to be > uppercase. I just want lowercase. How can I turn off this function? > Thanks > Ngoc > ...

Formatting
I've always used MS Word as my email editor in Outlook. Is this possible in Live Mail. Nope. I'm afraid that's only possible using Outlook. In WLM you have to use the built in editor. Is there anything in particular that's lacking from the built in editor that you're looking for? Colin Brown WL MVP "jrchambe" <jrchambe@discussions.microsoft.com> wrote in message news:5EC21892-D39F-4219-AB0F-47BC14E1CD36@microsoft.com... > I've always used MS Word as my email editor in Outlook. Is this possible > in > Live Mail. "...

Excel 2000 macro security Level
Hi! I am running an ASP.NET application which ideally opens an Exce spreadsheet with a macro included in it. When I try opening spreadsheet without a macro, everything works fine, but with macros, i hangs on the opening command. I tried to go to Excel 2000 and go t Tools->Macros->Security Level->Low but it still hangs when I try t open the macro spreadsheet. All works fine with the spreadsheet no containing macros, so it really is a question of macro security. But put it to low, so I don't understand how it is still not opening!! An help would be greatly appreciated! Thanks and...

Running an Excel Macro from an Access Command Button
Hi , Can anyone help me with this, I am trying to run an excel macro from an access command button. I want the macro to Bold the Headings on a excel spreadsheet. The code runs without any errors but the macro doesnt seem to work, I think I have got the line ExcelApp.ExecuteExcel4Macro "RUN(""PERSONAL.XLS!BoldHead"")" wrong The code: Private Sub cmdRunMac_Click() Dim ExcelApp As Excel.Application Dim ExcelBook As Excel.Workbook Set ExcelApp = Excel.Application ExcelApp.Visible = True Set ExcelBook = ExcelApp.Workbooks.Open("C:\Documents and Settings\...

New Named Range Created Each Time Data Imported into Excel via Macro
I have noticed that each time I import data into an Excel spreadsheet via a macro, a new named range (for the same range) is created. This does not pose a problem, but after a while, I'll have a huge number of named ranges that will never be used. Why does Excel name the range and how can I stop this? I noticed in the recorded macro, there was a line .Name = "drd_5". I commented this out to see what would happen, but it just renamed the range "ExternalData_5". The next one was "ExternalData_6", etc. Thanks, Carroll Rinehart You could refresh with differen...

Formatting Cells in Excel 97
Hi Guys, Sorry if I sound real stupid but is there anyway that we can control the column formatting in Excel 97 like let's say column A = GENERAL(6), column B = GENERAL(4), column C = TEXT(18) etc? Appreciate any form of advice, thanks!! I replied in the programming group. Are you seeking a programming answer? It's usually best to only post to one group, and include the remark "Please tell me if I should ask this in another group." On Thu, 28 Aug 2003 00:56:47 -0700, "Daryl" <daryl.ho@tnt.com> wrote: >Hi Guys, > >Sorry if I sound real stupid b...

Creating a worksheet in Spanish
Hello, Is there a way to create a worksheet on MS XP Pro running Office 2003 Pro all in Spanish? Thank You Kent Hansen Hi Kent All valid formulas -except the ones from the Analysis Toolpack- will automatically translate to the language of the Excel it's opened in. So any spanish speaking Excel will translate formulas to spanish, no matter which version it was written in. The rest, text constants and such, has to be typed or pasted. Excel will not translate this herself. HTH. Best wishes Harald "Kent" <Kent@discussions.microsoft.com> skrev i melding news:ACCCBA3A-53B4...

format a CD
Hi, how can I format a cd+rw re writeable disc aga ...

vba code to enable macros
Hi, Can we write a vba code to enable macros. In most of the sites it says we cant do that . The only thing we can do is change the macro setting or work around is hide the sheet which has macros. But none of them will work in my case. Can we change an excel security setting to from macro on open so that macros are enabled. No. If we can do that with code, then so could a hacker with bad intentions. Mike F "varsha12" <varsha12@discussions.microsoft.com> wrote in message news:DBEB1614-C956-491B-B351-02DD9F711891@microsoft.com... > Hi, > Can we w...

Macro on a protected worksheet in a shared workbook.
Hi, I have a macro in protected worksheets that can't run once the Workbook is Shared. I have wrapped the Macro code so as the worksheet is unprotected for the time the Macro runs, and unable the Autofilter. The code is as follow at the moment: Sub Newaction() Sheets("Critical Path").Unprotect ("") ' Newaction Macro ' Macro recorded 17/11/2004 by Clifford ' ' Selection.AutoFilter Field:=1, Criteria1:="=" Sheets("Critical Path").EnableAutoFilter = True Sheets("Critical Path").Protect contents:=True, userInter...

Remove format link?
Is there a way to remove the format link between fields in Modifier? Help just shows how to set it and the usual stuff doesn't seem to be working. ...

Word doc format
I tried to email a word attachment written on office for mac 2008 home version, but the recipient couldn't open the doc. recipient using windows xp. rlciii@officeformac.com <rlciii@officeformac.com> wrote: > I tried to email a word attachment written on office for mac 2008 home > version, but the recipient couldn't open the doc. recipient using windows > xp. If they don't have a recent version of Windows Office, they may not be able to open a .docx file. Choose File > Save As and save as a .doc file. -- Adam Bailey | Chicago, Illinois adamb@lull.org | Finge...

Currency Format Lost in Union Query
Thanks to John Spencer, I resolved one problem in my union query (Thank you, John). But I have another problem. Some of my fields in my union query are currencies and others percents. I see both formats are lost. I am sure it is a union query problem, because when I ran an individual query, none of the formats were lost. Is there any way I can keep currency and percent formats without writing, FORMATCURRENCY, etc for each field? Thank you. What data types are these fields? The field in a UNION query will normally take on the data type of the field in the first SELECT. So, if ...

Output to macro to sharepoint
Hi all i need help please, Using Access 2000 and up I have an update macro that is doing update to tables and produces a report that normally gets printed out. Now i need to stop printing reports and distribute them electronically. My idea is to have them in SNapshot format and push them to a SharePoint library. I have tried this several times and get 'The report snapshot was not created because you don't have enough free disk space for temporary work files'. It then goes on to tell me to empty my recycle bin. I have done a disk clean and nothing changed. Is it that i can't sa...

sort special text/numbers in format with many dots
Hi I need your help with sorting in Excel! I have mani Text fields with numbers into it. As example: 1 5.1 1.2 10.2.1 1.2.3 2 2.1.5 3 3.2.1.1 3.3.1.1.1 And it should sorted like this 1 1.2 1.2.3 2 2.1.5 3 3.2.1.1 3.3.1.1.1 5.1 10.2.1 How can I sort this like numbers? My problem is, that not all Numbers have the same format as x.x.x.x! And I can't change this Text-Fields to Numbers, because 10.6.1 looks the like 37052 :-( Any suggestions? Thx Marco Hi With your data in column A, insert a blank column at B. In B1 enter =IF(ISNUMBER(FIND(".",A1)),LEFT(A1,FIND(".&q...

How do I format cells to a specific number of digits?
I am trying to format a column so I can enter 5 digits only. It has between 5 and 7 digit numbers in it right now and I need to delete the remaining ones. It is about 350 numbers, so it would be a lot of work to go into each cell and delete the remaning numbers. Is therea way I can format the whole column to allow 5 digits? The same happened with letters. I had codes in it that consisted of numbers and letters and the client only wants the first two letters to remain. How can it be formatted so only 2 characters are allowed to keep me from having to enter each cell separately? Hi form...

date format #9
Hi everyone Is there any way to add automatically "rd" or "st" or "th" etc after the date. I have a date in A1 as "30-01-2001" and I need to show in B1 as "January 30th" Hope someone has an idea Thanks Toms --- Message posted from http://www.ExcelForum.com/ Hi SMILE! One Way. Use: =DAY(A1)&IF(AND(MOD(DAY(A1),100)>=10,MOD(DAY(A1),100)<=14),"th",CHOOSE (MOD(DAY(A1),10)+1,"th","st","nd","rd","th","th","th","th","th","th")...

How do I protect a macro to prevent change ?
I have protection turned on for both the worksheets and the workbook. Even so, the user can open Visual Basic Editor and change the macros. How to I protect the macros from being changed ? In Visual Basic go to tools > vga project properties > protection and you will see a section for pass words Try to keep the same pass for all of your work to make things easy if you need to get back in. cheers Pete -- (][ THIS EMAIL HAS BEEN SCANNED BY NORTON ANTIVIRUS ][) "Keisamcasco" <Keisamcasco@discussions.microsoft.com> wrote in message news:12E280B5-02DB-4F77-AD51-59EC0D...