Automatically control the font of a cell when a function is used

I have a my custom function say myfun(arguments)
I would like that whenever I use this function in any cell of a
workbook (sheet)
there should be always be the same font lets say Times New Roman, the
font size to be 10, font color automatic.
I tried to make another sub that will check every time the sub is
entered and ensure that the cell is well
with the desired font type. But, I could not get through the sub.
Any help in this regard will be appreciated.
Thanks in advance.
0
Subodh
5/5/2010 6:42:23 AM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
1049 Views

Similar Articles

[PageSpeed] 4

I don't think that you can make changes to the worksheet from within the UDF 
but you could use a Worksheet_Change event. It will only set the font when 
you enter or change the formula and there is nothing to prevent the user 
changing the format of the cell later.

Not sure of your expertise with VBA but just in case you need help. Right 
click the worksheet tab and select View code and the VBA editor will open at 
the worksheets code. Copy the code and paste into the worksheets code area.

Private Sub Worksheet_Change(ByVal Target As Range)
  If (Left(LCase(Target.Formula), 6)) = "=myfun" Then
    Target.Font.Bold = True
    Target.Font.ColorIndex = 3   'Red font
  End If
End Sub



-- 
Regards,

OssieMac


"Subodh" wrote:

> I have a my custom function say myfun(arguments)
> I would like that whenever I use this function in any cell of a
> workbook (sheet)
> there should be always be the same font lets say Times New Roman, the
> font size to be 10, font color automatic.
> I tried to make another sub that will check every time the sub is
> entered and ensure that the cell is well
> with the desired font type. But, I could not get through the sub.
> Any help in this regard will be appreciated.
> Thanks in advance.
> .
> 
0
Utf
5/6/2010 8:04:02 PM
On May 7, 1:04=A0am, OssieMac <Ossie...@discussions.microsoft.com>
wrote:
> I don't think that you can make changes to the worksheet from within the =
UDF
> but you could use a Worksheet_Change event. It will only set the font whe=
n
> you enter or change the formula and there is nothing to prevent the user
> changing the format of the cell later.
>
> Not sure of your expertise with VBA but just in case you need help. Right
> click the worksheet tab and select View code and the VBA editor will open=
 at
> the worksheets code. Copy the code and paste into the worksheets code are=
a.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> =A0 If (Left(LCase(Target.Formula), 6)) =3D "=3Dmyfun" Then
> =A0 =A0 Target.Font.Bold =3D True
> =A0 =A0 Target.Font.ColorIndex =3D 3 =A0 'Red font
> =A0 End If
> End Sub
>
> --
> Regards,
>
> OssieMac
>
>
>
> "Subodh" wrote:
> > I have a my custom function say myfun(arguments)
> > I would like that whenever I use this function in any cell of a
> > workbook (sheet)
> > there should be always be the same font lets say Times New Roman, the
> > font size to be 10, font color automatic.
> > I tried to make another sub that will check every time the sub is
> > entered and ensure that the cell is well
> > with the desired font type. But, I could not get through the sub.
> > Any help in this regard will be appreciated.
> > Thanks in advance.
> > .- Hide quoted text -
>
> - Show quoted text -

Thanks
0
Subodh
5/7/2010 9:17:33 AM
Reply:

Similar Artilces:

Subformss, Tab Controls, and Forms
Hello I am creating a case management database. I have created a Main form called Participants. There are several themes which must be completed for each participant: Personal Information, Personal Assessment, Personal Plans, Notes, and Other. I thought of using a tab control for each theme. I want to create subforms for each theme. That is, for the Personal Information tab, I want to create subforms for each subject area, etc. I read on this board that you can't but a tab control within a tab control, but you can create subforms that would individually contain a ta...

Tree control item is not selected.
Hi there, I found a suprising behaviour when showing a modal dialog inside OnLButtonDown of a tree view. When I do that on a new tree item it's not gonna be selected. The old item is still highlighted. Is that the desired behaviour? Here is what I do inside OnLButtonDown: void CTreeCtrlView::OnLButtonDown(UINT nFlags, CPoint point) { AfxMessageBox( "Hallo" ); CTreeView::OnLButtonDown(nFlags, point); } When I comment out AfxMessageBox() call. The new item will be selected. Any ideas? Regards, Christian > Hi there, I found a suprising behaviour when showing a ...

How come there are more replies when using a newsreader
I was wondering how come there are more replies in this group when using a newsreader. I now like using a newsreader to view this group but prefer posting in the web based interface. I have noticed that recently there are a lot more replies to topics that are not showing up in the web based interface but are showing up in the newsreader. Does anyone know if this is related to Microsoft starting to close down the newsgroups or is there another reason. Thanks in advance for the replies. It's due to longstanding (i.e., well over a year) synchronization issues (i.e., the sl...

Is there an equation in Excel to use multiple if then statements?
Please post your question in the body of your message, not just in the heading. Some systems cannot cope with "empty" messages. Look in HELP for AND and OR, or post more details of what kind or multiple if statement you need. -- Kind regards, Niek Otten "Cody5" <Cody5@discussions.microsoft.com> wrote in message news:E9F7AF01-ABDB-4AD3-8C54-4D1766F3E633@microsoft.com... > what do you mean by "multiple if then statements"? You can nest them: =IF(condition1, true1, IF(condition2, true2, false2)) you can have multiple if's sequential...

Index
Hi, I created a primary key when I created my table. I now want to create an index on multiple fields. How do I do that and how do I use this new index in a quary Thank you. Avaya Assuming Access 2003 or earlier, to create a multi-field index, either click on the Index icon on the button bar (it's a bunch of horizontal lines with a lightening bolt to the right), or select Indexes from the View menu. Pick a name for the index (it doesn't matter what you call it) and put that in the first column of the first empty row. In the next column, put the name of the first field you...

Pivot Table Cell Formatting
A pivot table "seems" to want to define all the cells within the table as numbers. Several columns of data within my source data are character (i.e. Y/N). The pivot table displays all Y and N's as a 1 when the cell is defined as a count. If you change to a sum or any other "available" types, they display as 0's. I can't seem to get around this. I want the Y or N value displayed. Any ideas or suggestions. Thanks, Jim G. Hi Jim the data section of a pivot table can (AFAIK) only consist of (aggregated) values. That is counts, sums, averages, etc. You're not ...

Error 553 using Outlook 2007 and Vista
Hello. I'm getting the following error using Outlook 2007 and Vista: 553 sorry, that domain isn't in my list of allowed rcpthosts (#5.5.3 - chkuser) When I test the account settings, it's fine. I can recieve just fine, and the only outgoing messages without this error are for recepients with my same domain. I've turned off my antivirus and windows firewall. Still this doesn't work. Any help would be greatly appreciated. 550 errors usually mean you're not authenticating properly, or you're attempting to use an SMTP server other than the one your ISP provides. ...

Anyone using GP for a Non-Profit org?
We are a very small Non-profit org (NPO) that needs to modernize so we operate more like a business. I am working on funding a SBS 2003 deployment. Our needs are: a bookkeeping system with various levels of access (our bookkeeper/office mgr, Exec. Dir., staff) that includes grant and donor/membership functionality; grants management; fundraising/donor management; central file and database storage for security, backups, and sharing. We currently use QuickBooks Pro 2003 for NPO's for bookkeeping and have no system in place (essentially) to handle grants, donors, or members. Our ...

Copy to Clipboard using Dexterity
Hi, I want to override the Line_Pre event to copy the Item No to the clipboard. Is it possible to copy a text field' value to clipboard in Dexterity? Thanks. MH. Why would you want to do this? If you are attempting to 'save' the item number value somewhere before you override with your script won't it make sense to store it to a global variable or a local variable? Working with the clipboard is simply not practical. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP B...

Create Graphs using VBA
Hello, I trying to implement a routine to create Powerpoint slides using a function from Microsoft Knowledge base 200551. There are 2 prolblems I am having with this that I'm hoping someone here can help. I have included the code from this site below. The first one is the routine uses early binding to the the Powerpoint library so the intellisense doesn't work. I am going to need to make some modifications to this such as passing labels, changing chart types etc. and it would relly help if the intellisense was working. My efforts to change this have not been succe...

When to use CCriticalSection?
I have an app in which there are 4 threads running simultaneously. Right now am using "Events" to do the synchronization between threads. Like, there is a Server writing thread and then there is a Server reading thread. Once Read thread, reads data from Server, it will Set an Event for Write thread to continue. So my question is, In this scenario do i need to use CCriticalSection? >I have an app in which there are 4 threads running simultaneously. >Right now am using "Events" to do the synchronization between >threads. > >Like, there is a Server writing ...

Put Results in another cell
OK guys/gals - I know this MUST be simple, but I'm very new to excel. I'm trying to look at one column (A), if the work "Dist" or "Sale" is in the Cell, I would like to fill the next column (B) with "--", otherwise, leave the values alone in column B. =IF(OR(A1="Dist",A1="Sale"),B1="'--", otherwise leave value alone in B1 Thanks for everything! To clarify, I would like my formula to reside in column (B), but there is other info already in that column. If the condition in column (A) is met, then (B) must change, ot...

Excel using =RC[-1] format and I don't want it
On this machine only, Excel will use the format =RC[-1] when I am entering a formula on the command line by hand and click on a cell to reference it in the formula. I don't know how this got on but I would like it to go off. On my other machines, it does the old familiar A1:B2 style of cell reference. I can't even search for how to fix it since I can't determine what this is called so I can turn it off. The only reference I have seen is a button that is only effective for macro creating and I am not creating a macro, just a formula, interactively, in the cell. How ...

Macro to select & autofill adjacent cells
I would like to create/use a macro that selects a cell to the right of highlighted cell and the two directly below. I then want to autofil down this selection. I've tried to record a macro for this, but i always performs the task at the same range - but I want it to wor wherever I choose to highlight a cell from. Does anyone know how thi can be accomplished? Sincerely, Ti -- Message posted from http://www.ExcelForum.com Hi! Yes: lots of people will know how;) Why not post your macro: someone will show you how it can be tuned t do more than just fill the three cells you recorded it f...

Master image to use in separate sheets?
Hello All, Does anyone know if there is a way to tell Excel to use the same image/background for each sheet, instead of needing to import for each one? Or is there a way to set up a (similiar to Power Point/Master slide). Thanks in advance, drew If it's just for one workbook, I'd have a master worksheet in that workbook that I could just copy. If it's for lots of workbooks, I'd set up a workbook with one sheet that had that background the way I wanted it. Then save this workbook as a Template (let the folder default to excel's favorite). Tnen when you rightclick on...

font resolution
I've created a business card and had it printed in an office supply store copy center. The font I used was Arial. The lettering appeared to be fuzzy. On close examination, I'd describe the font as being comprised of many little boxes giving a stair-stepped appearance on rounded or diagonal parts of letters. How do I avoid this and get a smooth edge on the letters? How did you save the card before you sent it to the printer? If you saved the card as a picture what you are seeing is a bitmap phenomenon. Can you save as a PDF? If you have 2007 Microsoft has a save as PDF add-in...

Data showing up in Cells as ####
I'm trying to enter an amount in a Column labeled "Limit"...I've tried changing the cell's format but no matter what I do, the data shows up as ####. How can I change this to reflect the actual numbers? -- MCD Increase the column width >-----Original Message----- >I'm trying to enter an amount in a Column labeled "Limit"...I've tried changing the cell's format but no matter what I do, the data shows up as ####. How can I change this to reflect the actual numbers? >-- >MCD >. > Or reduce the Font size?? anonymous@discussi...

Use query for row source
Similar to JHC message and I tried fredg recommendations with no luck. Running Office 2003 Professional Edition 2003 Converted database from 2000 to 2003 (problem existed in 2000 as well) Created Combo Box w/control wizard I can create the Combo Box usin the wizard, select required fields (only need one but tested to see if it happens w/all fields which it doesn't. I can see data for column one, primary key) and while still in the wizard, the combo box is displaying all information I'm looking for. Once I finalize wizard, switch to form view, the only data displayed in the comb...

How To Find "x" In A Column And Show "y" In Cell "z"
Example: A1 = aircraft type B1 = flight time C1 = jet category Is there a way to maintain a list somewhere in the book of aircraf types that are of the jet variety so that when "A2" is checked agains the list it knows whether or not it is in fact a "jet" and will ente the time (which is in "B2") under the jet category ("C2"). Otherwis it simply will ignore adding flight time to the "C" column? Did I just confuse everyone? Thanks in advance. thanks r -- rcan ----------------------------------------------------------------------- rcane&...

How can you identify folders, using folder numbers?
Hi everybody, I've searched and searched and asked in the quasi-defunct yahoo exchange newsgroup, but I've never found an answer as to how I can actually utilize the folder identification information contained in Exchange error log events. An example is as such: Event: 1173 Source: MSExchangeIS Mailbox Store Description: Error 0x6bb deleting unused restricted view from folder 1-1A42FF on database "First Storage Group\SG1 Mailbox1". It would be insanely useful if I could tell which folder 1-1A42FF is, so I can narrow down where the problem is occurring, you know?? Anyone...

Missing sections in CRM 3.0 using XP
I have upgraded my computer to XP PRO Sp2 and now I'm missing some sections, namely Setting on the left side of the screen. The Setting menu is in the GOTO menu but it doesn't work. I get a blank screen. Also the Articles function is missing in the NEW Menu. I can go to another computer running WIN2KPro and it works fine. I'm pretty sure it's just a setting in the Internet Options of IE but I have compared and don't notice a difference. Yes, I have turned off the local firewall. Ernest ------=_NextPart_0001_7245F7CE Content-Type: text/plain Content-Transfer-Encod...

Using on-the-fly in a report?
Hi I have 2 txt boxes in my form that do calcualtions using the control source. For example the txt box margin has a control source of netcost-jobcost (which are also txt boxes). As the two txt boxes results are not stored in a table can someone please tell me how I can get the results to show in any report that has "margin" as a field. What control do I use for it to pick up the results from the calculation that was made in the form. Not every report has both netcost and jobcost in. Should I put them both in there and make them invisible and then do the calculation again in the con...

Using Two Company Name / Addresses in GP 9.0
We have our parent company set up as the main name in GP but want to use a dba name on Quotes/Orders/Invoices in SOP. Is there any easy way to do this? I had been working with a consultant who said we need to modify the report and hard code the information in. So I set up the system to use the "other" forms. I have done all the modifying several times and set up security to use the report. After saving everything and exiting GP - I come back in and it can't find the report. I think the problem has to do with our Citrix environment and what report dictionary it is looking a...

Displaying only specified values-not using Autofilter
I have a somewhat unusual question based on something that's stumped me for over a week. I'm hoping the collective wisdom of this newsgroup might be of some help. I'm attempting to display all of the results of value "x" from one sheet on another. To elaborate, sheet 2 has a column, let's call it column A. Each cell in column A has a value, let's say, for simplicity sake, "1", "2", "3", or "4". What I want to do is go back to sheet 1 and format it in such a way that all of the rows containing value "1" in the afor...

duplicate cells
I have a spreadsheet of Names and addresses. the list is very large (about 8000) there are quite a few duplicate entries that I would like to eliminate. is there a formula or a uncomplicated method that will do this for me? bear in mind that I am a novice with excel spreadsheets.d appreciate any help. Thanks Richard, have a look at Chip's site for a way to do it. http://www.cpearson.com/excel/deleting.htm#DeleteDuplicateRows You may also what to have a look here on getting started with macros http://www.mvps.org/dmcritchie/excel/getstarted.htm if you need more help post back with...