How to format the value in a cell?
Hello! I wonder if excel has the function to format a value in cell without the help of Macro. To format a value,here I mean to correct the value automatically. For example,it need the value in a cell to be "T".But a user may enter " T","T ","t".The formatting function used here to trim the space at the begining or end of "T",or change the "t" to upper case. -------------------- An
abc8969
excel
39879 articles. 
2 followers.
13 Replies
1557 Views
Try =TRIM(UPPER(A1)) -- Regards Roger Govier "An" <abc@sohu.com> wrote in message news:u%23Kg%23X6hFHA.576@tk2msftngp13.phx.gbl... > Hello! > I wonder if excel has the function to format a value in cell > without the help of Macro. > To format a value,here I mean to correct the value automatically. > For example,it need the value in a cell to be "T".But a user may > enter " T","T ","t".The formatting function used here to trim the > space at the begining or end of "T",or change the "t" to upper case. > -------------------- > An > > > > >
roger5293You can't do this with formatting alone.
You'll need a macro.
If you want a macro, right click on the worksheet tab that should have this
behavior. Select view code and paste this into the code window:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
On Error GoTo errHandler:
If IsEmpty(Target) Then
'do nothing
Else
Application.EnableEvents = False
Target.Value = "T"
End If
errHandler:
Application.EnableEvents = True
End Sub
================
Actually, you can change the display to just show T--but it won't change the
value.
You could give the cell a custom format of:
T;T;T;T
But if you want to check that cell, you can't use:
=if(a1="T","ok","not ok")
But you could check to see if the cell was empty:
=if(a1<>"","ok","not ok")
An wrote:
>
> Hello!
> I wonder if excel has the function to format a value in cell
> without the help of Macro.
> To format a value,here I mean to correct the value automatically.
> For example,it need the value in a cell to be "T".But a user may
> enter " T","T ","t".The formatting function used here to trim the
> space at the begining or end of "T",or change the "t" to upper case.
> --------------------
> An
--
Dave Peterson
petersodHi An, Except for some odd cases ( http://j-walk.com/ss/excel/odd/odd06.htm ), the formulae in Excel can not insert, delete objects or change their properties. They can only return values. So you can't change the original cell value but you can of course get theammended value in a different cell. Just explore the Help for the following functions: TRIM() - removes extra spaces CLEAN() - removes non-printable characters UPPER() - coverts to upper case LOWER() - coverts to lower case PROPER() - capitalizes the first letter TEXT() - returns text in many different formats and many other text functions Regards, KL "An" <abc@sohu.com> wrote in message news:u%23Kg%23X6hFHA.576@tk2msftngp13.phx.gbl... > Hello! > I wonder if excel has the function to format a value in cell > without the help of Macro. > To format a value,here I mean to correct the value automatically. > For example,it need the value in a cell to be "T".But a user may > enter " T","T ","t".The formatting function used here to trim the > space at the begining or end of "T",or change the "t" to upper case. > -------------------- > An > > > > >
NOSPAMlapink2000Thank you! Mr. Peterson. To use Macro is hard for an average person.I think this function is an useful one. Why Microsoft didn't integrate it into Excel to cell's formatting,let user use some functions ,say,Upper(),Lower(),Trim() to format the value? -------------------------------------------------------------------- An
Formats change the look of a cell--not the value. all those =upper(), =lower(), =trim() actually can change the value. You could use those functions in an adjacent helper cell, though. Use one column for input and the adjacent column for what you think the input should have been. An wrote: > > Thank you! Mr. Peterson. > To use Macro is hard for an average person.I think this function is an > useful one. > Why Microsoft didn't integrate it into Excel to cell's formatting,let user > use some > functions ,say,Upper(),Lower(),Trim() to format the value? > -------------------------------------------------------------------- > An -- Dave Peterson
Thank you for telling me the basic rule.but I don't think it break the rule if this occurs during a user enters the value.
Yes,use an adjacent cell,but not a good alternative,I think.
Well, it actually does if you want the value changed. As Dave has already explained in his recent message to you, a format is not changing the value, but the look and thus doesn't break the basic rule. Regards, KL "An" <abc@sohu.com> wrote in message news:uI3RPk7hFHA.2360@TK2MSFTNGP10.phx.gbl... > Thank you for telling me the basic rule.but I don't think it > break the rule if this occurs during a user enters the value. > >
I learned that there are two states while the value is being entered,before and after the entered value is accepted by a cell. I think the basic rule is for the accepted value.So changing the value before it was accepted doesn't break the rule. Regards, An
An, What you are saying is true for anything, but functions. For a function a non-accepted value is non-existant, it will see only the accepted value. BTW as far as I know, VBA has no built-in way of reading non-confirmed value from a cell either. In a sense, there is something in Excel that could be seen as a prototype of what you are after and it is the AutoCorrect (menu Tools>AutoCorrect). See if you can get it to do whatr you want. Regards, KL "An" <abc@sohu.com> wrote in message news:O2$r7A8hFHA.3012@TK2MSFTNGP12.phx.gbl... >I learned that there are two states while the value is being entered,before > and after the entered value is accepted by a > cell. > I think the basic rule is for the accepted value.So changing the > value before it was accepted doesn't break the rule. > > Regards, > An > >
AutoCorrect is for all the cells,not for a certain part of the cells. And,in some cases,AutoCorrect is not convenient. Thanks!I asked the question because I used to learn a software logically,Excel should have the function. Regards, An
I agree, the inability to "force" a case setting in Excel data entry i a problem. :mad: In Canada, postal codes are in the format A1B 2C3 while uppercase letters are not strictly required, it looks odd if the are not used. Things would be so much simpler if I could use a tex format on a cell to "force" the characters to be uppercase, instead o having to use a macro (which the user may choose to disable) or function (which is a "kludge" solution). Microsoft, are yo listening? One "workaround" that I have used is to change the cell font t something that displays all caps regardless of the key entered. Ban Gothic, Bremen, Charlesworth and several others on my computer hav this capacity. With this option, even if you type "a1b 2c3" it wil display as A1B 2C3. Whether or not this works for you will depend o what you plan to do with the text afterward - simple printing will b fine, but any data manipulation may force you to use the UPPER functio to get the results you need -- SuperDav ----------------------------------------------------------------------- SuperDave's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2593 View this thread: http://www.excelforum.com/showthread.php?threadid=38675
SuperDave.1t9cyb_1123189506.084There's a very good chance that MS is not listening. You may want to send your request to: mswish@microsoft.com SuperDave wrote: > > I agree, the inability to "force" a case setting in Excel data entry is > a problem. :mad: In Canada, postal codes are in the format A1B 2C3; > while uppercase letters are not strictly required, it looks odd if they > are not used. Things would be so much simpler if I could use a text > format on a cell to "force" the characters to be uppercase, instead of > having to use a macro (which the user may choose to disable) or a > function (which is a "kludge" solution). Microsoft, are you > listening? > > One "workaround" that I have used is to change the cell font to > something that displays all caps regardless of the key entered. Bank > Gothic, Bremen, Charlesworth and several others on my computer have > this capacity. With this option, even if you type "a1b 2c3" it will > display as A1B 2C3. Whether or not this works for you will depend on > what you plan to do with the text afterward - simple printing will be > fine, but any data manipulation may force you to use the UPPER function > to get the results you need. > > -- > SuperDave > ------------------------------------------------------------------------ > SuperDave's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25935 > View this thread: http://www.excelforum.com/showthread.php?threadid=386752 -- Dave Peterson
Similar Artilces:
Cell searching and Replacing
Hi Could use some help here please have a movie list someone sent to me and it has cells like Age of Innocence, The Bone Snatcher, The Etc Etc I would like to do a Next procedure and Replace the text , The and put the The in Front of the text So it is like this The Age Of Innocence The Bone Snatcher Etc Etc Hope someone can be kind enough to help me Regards Graham Try =IF(RIGHT(A1,5)=", The",RIGHT(A1,3)&" "&LEFT(A1,LEN(A1)-5),A1) Hope it helps. Graham Feeley Wrote: > Hi Cou...
copy cells from one worksheet to another
How do I make the "value" or content of a cell on 'worksheet 1' mirror or copy the font color (and strike through) of a cell on a seperate worksheet? - 'worksheet 2'? thanks. Copy and paste special|values followed by Paste special|formats If you were using a formula, then that formula can only return the value--it can't change the format. Depending on how that other cell changed, you could have an event macro do the work for you. Garrett wrote: > > How do I make the "value" or content of a cell on 'worksheet 1' mirror or > copy ...
Contents of cell copied to another sheet in case combo box value is choosen
I have a value range in a row (worksheet 1). That range is listed via Data > Name and Validation on another worksheet (2) How to have the name of cell A1 of Worksheet 2 mentioned in Worksheet 1 if the particular value is choosen? So: Worksheet 1: 10 20 30 X 40 Worksheet 2 (cell A1 = "Table"): I choose via combo box value 30. Neigboring cell X changed into "Table". Bart Excel 2003 ...
Vlookup based on Active cell
Is there a way to have a vlookup formual based on the data in the active cell. I have data on one sheet that I what to pull in to a cell on another worksheet based on the data in the active cell I click on. I looking for the reference of the vlookup to be the active cell. thanks in advance for any help on this Thomp <williamth...@gmail.com> wrote... >Is there a way to have a vlookup formual based on the data in the >active cell. I have data on one sheet that I what to pull in to a cell >on another worksheet based on the data in the active cell I click on. >I looking for the...
Copying worksheet but changing format
Is it possible to copy one worksheet into a new one but switch the headings to be on the left vertical and vice versa? Or must I manually recreate the whole worksheet in this new set-up? Thanks I think you probably need Paste Special and Transpose which takes headings from Row and puts in column or vice versa. HTH Sheila "Glissader" wrote: > Is it possible to copy one worksheet into a new one but switch the headings > to be on the left vertical and vice versa? Or must I manually recreate the > whole worksheet in this new set-up? > > Thanks ...
comparing dates cells
I'm comparing a date from one cell to a date within my formula. I've tried using datevalue but that doesn't work either. How do you do this? My formula reads as: =IF(A2>1/1/2003, "yes", "no") Where A2 is an entered date ie: 5/1/2003 Thanks, Ben =IF(A2>"1/1/2003","yes","no") -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "ben" <ben@2ndsource.com> wrote in message news:01d301c368e3$fed01320$a501280a@phx.gbl... > I'm comparing a date from one cell to a date within my > form...
Finding a string in Excel cells
Not sure if this is an Excel problem or a general Microsoft products "feature", but here goes:- I've inherited a HUGE spreadsheet with 6 figures of cells in total(!) and I'm told that some have been highlighted for query or error with "???" in the cell (in addition to other text in that cell). Trouble is, as soon as I do "find" with the "?" character, it seems to ignore it, as if ? is some reserved character. Does anyone have any idea how I can get round this? Ta! The ? character is used as a wild card character (any single character). ...
Conditional Format Negative to Postiive Numbers w/3 Symbol Icon Se
Hi! I am using Excel 2007 and would like to use conditional formatting (icon sets-3 symbols) for a range of various negative through positive numbers. I would like to see a result of a green symbol for any value between -10% to 10%, yellow symbol for any value between -11% to -50% and 11% to 50%, and a red symbol for any value between -51% to -100% and 51% to 100%. How can I do this? This is a problem I encountered when I tried to add a new rule for the negative numbers range through the conditional formatting rules manager, I received this message: "One or more of th...
How to apply budget for earned value analysis
I'm having huge problems finishing a plan I'm working on. Maybe someone here has some clever ideas. To take it from the beginning: This is a part of some school work, and I have a number of tasks, and for each of the tasks I have details on what the budget is, and the planned duration. At a specific point in time I have details on the completion rate of each task, as well as the money spent, and the actual duration. From these figures, I want MS Project to calculate figures such as BCWS, ACWP, BCWP, EAC, SPI and CPI. My problem is that I have not found a place to enter th...
Maintaining Number Formatting when merging from Access
Hi - I am merging from a pre-existing Access database into Word (both v2007) and noted that the currency formatting applied in the Access dbase is not being carried over into the Word document (currency sign, # of decimal places, etc.). Any ideas on how to solve this problem? Thanks, Chris Hi Chris, To control number & currency formatting in Word, add a numeric picture switch to the mergefield. To do this: .. select the field; .. press Shift-F9 to reveal the field coding. It should look something like {MERGEFIELD MyData}; .. edit the field so that you get {MERGEFIELD MyD...
Default value for Combo box on a Subform
I am having difficulty using VBA to set a value for a combo box control that resides on a subform based on the value in a text box control on a parent form. I know how to do this for textbox controls but Access 2003 does not seem to allow me to do this with a combo box using the same method. It returns the error "You can't assign a value to this object". So how do I assign a value to a combo box then? Below is my code: Private Sub Form_Open(Cancel As Integer) Dim strtable As String Dim strtable2 As String Dim getvenueid As Integer Dim sql As String Dim sql2 As String st...
default value with phone numbers
Hi, I would like to know if its possible to set a default value in the standard phone number field(s). To have access to numbers when calling from abroad, one needs to put +31 in front of the phone numbers. So the number 045 xxx xx xx would become +31 45 xxx xx xx. My question, is it possible to set this +31 as default value, so only the 45 xxx xx xx has to be types during input of phone numbers? Thanks Hi. Yes, there is the way. =) Take a look into SDK. There is are good examples of client-side customization. For Your question: in form constructor You must enter properties of main ...
go to cell with date equaling TODAY()
Hello! I have a spreadsheet with January 01, 2006 thru December 31, 2006 in ascending order in column A, each date in a different cell (A1, A2, A3, etc.). I don't enter data into this sheet everyday..in fact, months could go by before having to enter an occurance for say, March 31. Is there a way to have excel, upon opening the spreadsheet, advance the cursor to the cell with that day's date in it? -- Thank you all for your help! Using function Date rather than Today() Works for me. Private Sub Workbook_Open() Dim r As Long Dim T As Long T = Date r = Application.Matc...
This works except when both cells are blank.
Every once in a blue moon I get to work on a personal project. This is for track of some scores. I modified the formula from some code in the ng archives but haven't figured out how to take care of when both cells are blank. In E2: =IF($C2>$D2,"YOU WON!!",IF($C2<$D2,"You Lost ... :o(","Tied!")) this works great unless both C2 and D2 are blank when I get "Tied!" whereas should be blank. How do we accommodate for blank cells in the above formula? Thanks! :oD =if(count($c2:$d2)<2,"not enough scores", ... You could use =count...
How do you use "Block Formatting" for column headings?
I want to use it to Left Justify/set up on large spreadsheet, so that the second word falls right under the first. I am having trouble. Try this: Enter the first word, hold down the ALT key and press ENTER. Th cursor will move to the next line in the cell. Release the ALT key. Now enter the second word. Simply press ENTER. One cell, two lines. Use Format>Cells to make sure it is Left Aligned. HTH Bruc -- swatsp0 ----------------------------------------------------------------------- swatsp0p's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1510 View...
I need to shift cells down only to a point w/o disturbing the bot.
I have a spreadsheet in which the cells need to be moved down, however only to a point. If I move everything down it disturbs the bottom row of totals. Can anyone help me? I think you need to provide a few more details about what exactly you are trying to accomplish. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "wade2753" <wade2753@discussions.microsoft.com> wrote in message news:D905FEE5-2EA7-47A1-A532-4D0CA743121C@microsoft.com... >I have a spreadsheet in which the cells need to be moved down, >however o...
Time format 1500 become 15:00
Pls help me. I want to tape 1500 on A1 and A1 edit 15:00 Thanks for your answer Oh Yes, thanks so much. "מיכאל (מיקי) אבידן" wrote: > Use 'Custom Format': ##\:## > Micky > > > "tran1728" wrote: > > > Pls help me. > > I want to tape 1500 on A1 and A1 edit 15:00 > > > > Thanks for your answer > > Dear Micky, thanks again, I have a other question, si possible for Macro, The cursor go directy to the last row+1 of column A thanks for your reply "מיכאל (מיקי) אבידן" wro...
Money SDK for QIF format converter?
The brokerage that administers our company 401K downloads transaction data in the QIF format only. They only support downloading all transactions without specfying a date. So being an angry developer I started writing a conversion program in C# to convert the QIF into OFX and to automatically track and eliminate duplicate data. I have the QIF format and the OFX spec but I was wondering if Money has an SDK anywhere that would speed up the process for me. I have an MSDN Universal subscription but did not see anything in there. Thanks See http://umpmfaq.info/faqdb.php?q=10. "A...
Convert Positive Value to Negative Value
How can I get the "Amount" to be a negative number when the "Code" = "G6"? Maybe I could multiply by -1 and use the CASE statement, but I don't know how. Table Name = EncounterData Amount Code $15.00 G2 $13.00 G2 $18.00 G6 $16.00 G2 $25.00 G6 $30.00 G6 -- Tyro from Missouri You're right, use a CASE, for example Select Case When Code = 'G6' Then -1 * Amount Else Amount End As Amount, Code From EncounterData; Tom "Tyro" <Tyro@discussions.microsoft.com> wrote in message news:20959474-72BF-454D-8820-...
Error: You tried to assign a null value...
Hi Folks! I'm having trouble trying to figure out the error "You tried to assign a null value to a variable that is not a variant type." I have a NotInList event in a combobox (LastName_cbo) of a subform (sfrAttendee) on a form (frmAttendance). A message box pops open when a new name is entered into the combobox. Clicking "yes" opens a new form; clicking "no" generates the error. What I want to happen when I click "no" is for the field to be returned to its "clean" state with nothing in it, so that my user can start fresh...
Adding a rectangle to a table cell
I would like to add a rectangle to a table cell. However, when I try to do so I receive an error message stating the "Object does not support this action". Could someone please provide me with an example of: Adding a rectangle to a table cell Adding some text to the rectangle Setting the Back and Fore color of the rectangle Thanks to all in advance and have a Happy New Year! Brian For all intents and purposes, a table cell IS a rectangle. You can add text directly to it, set its color and so on. Depending on what you're after, you could add a rectangle atop...
Time formats
I am creating a template which requires the user enter a time (not present time, flight times), for example the user will be listing a number of flight arrivals and departures and what I want to do is have the user simply enter something like 804p and have the cell display it as 8:04 pm. I have looked at some custom formats and nothing seems to be able to do this. Any help would be appreciated. Thanks! Tracey ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ See http:...
Allow any Certain chr within a cell
Hi My question is in two parts:- 1. How do I restrict the contents of a cell a single chr namely "A to F" or 1-9" 2. Since the cell is only allowed to be 1 digit long when I have keyed and checked it moves on to the next cell automatically Thanks in advance Trev Hi Trever, Use cell validation: -- Part 1 of question Data (menu), validation allow: Text length Data: between Minimum: 1 Maximum 1 [ x] ignore blanks After you have tested with the above to see the error produced, would refine the error message by continuing with "Input Mess...
Looking for Datmax label format
Hi. Has anyone had success printing price labels with a Datamax printer? I have tried using the RMS Label Designer, but have not had much success. I'm able to query data and send it to the Bar Tender program via access, but that is very labor-intensive. Anyone have a working template I could look at? Failing that, any advice on how to develop my own template or find another way to print labels directly from RMS would be appreciated. I'm using a Datamax E-4203. Thanks Since no one answered my post, taking the bull by the horns, I opened up the label designer. Since I need priceta...
Changing values
I have a many-to-many relationship between Outings (with a primary key of EventID) and Members (with a primary key of MemberID). There is a table that ties the 2 together, tblAttendees that holds the EventID and MemberID. Recently, the MemberID of the tblAttendees changed on 2 records in the table. I can see the before in an earlier version of the database and can see what changed in the current database. It appears to be the first event in the tblAttendee which is changing when a new member is added. Am I doing something wrong or ?? Jeffrey Marks wrote: >I have a many-to-many ...
Hi Could use some help here please have a movie list someone sent to me and it has cells like Age of Innocence, The Bone Snatcher, The Etc Etc I would like to do a Next procedure and Replace the text , The and put the The in Front of the text So it is like this The Age Of Innocence The Bone Snatcher Etc Etc Hope someone can be kind enough to help me Regards Graham Try =IF(RIGHT(A1,5)=", The",RIGHT(A1,3)&" "&LEFT(A1,LEN(A1)-5),A1) Hope it helps. Graham Feeley Wrote: > Hi Cou...
copy cells from one worksheet to another
How do I make the "value" or content of a cell on 'worksheet 1' mirror or copy the font color (and strike through) of a cell on a seperate worksheet? - 'worksheet 2'? thanks. Copy and paste special|values followed by Paste special|formats If you were using a formula, then that formula can only return the value--it can't change the format. Depending on how that other cell changed, you could have an event macro do the work for you. Garrett wrote: > > How do I make the "value" or content of a cell on 'worksheet 1' mirror or > copy ...
Contents of cell copied to another sheet in case combo box value is choosen
I have a value range in a row (worksheet 1). That range is listed via Data > Name and Validation on another worksheet (2) How to have the name of cell A1 of Worksheet 2 mentioned in Worksheet 1 if the particular value is choosen? So: Worksheet 1: 10 20 30 X 40 Worksheet 2 (cell A1 = "Table"): I choose via combo box value 30. Neigboring cell X changed into "Table". Bart Excel 2003 ...
Vlookup based on Active cell
Is there a way to have a vlookup formual based on the data in the active cell. I have data on one sheet that I what to pull in to a cell on another worksheet based on the data in the active cell I click on. I looking for the reference of the vlookup to be the active cell. thanks in advance for any help on this Thomp <williamth...@gmail.com> wrote... >Is there a way to have a vlookup formual based on the data in the >active cell. I have data on one sheet that I what to pull in to a cell >on another worksheet based on the data in the active cell I click on. >I looking for the...
Copying worksheet but changing format
Is it possible to copy one worksheet into a new one but switch the headings to be on the left vertical and vice versa? Or must I manually recreate the whole worksheet in this new set-up? Thanks I think you probably need Paste Special and Transpose which takes headings from Row and puts in column or vice versa. HTH Sheila "Glissader" wrote: > Is it possible to copy one worksheet into a new one but switch the headings > to be on the left vertical and vice versa? Or must I manually recreate the > whole worksheet in this new set-up? > > Thanks ...
comparing dates cells
I'm comparing a date from one cell to a date within my formula. I've tried using datevalue but that doesn't work either. How do you do this? My formula reads as: =IF(A2>1/1/2003, "yes", "no") Where A2 is an entered date ie: 5/1/2003 Thanks, Ben =IF(A2>"1/1/2003","yes","no") -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "ben" <ben@2ndsource.com> wrote in message news:01d301c368e3$fed01320$a501280a@phx.gbl... > I'm comparing a date from one cell to a date within my > form...
Finding a string in Excel cells
Not sure if this is an Excel problem or a general Microsoft products "feature", but here goes:- I've inherited a HUGE spreadsheet with 6 figures of cells in total(!) and I'm told that some have been highlighted for query or error with "???" in the cell (in addition to other text in that cell). Trouble is, as soon as I do "find" with the "?" character, it seems to ignore it, as if ? is some reserved character. Does anyone have any idea how I can get round this? Ta! The ? character is used as a wild card character (any single character). ...
Conditional Format Negative to Postiive Numbers w/3 Symbol Icon Se
Hi! I am using Excel 2007 and would like to use conditional formatting (icon sets-3 symbols) for a range of various negative through positive numbers. I would like to see a result of a green symbol for any value between -10% to 10%, yellow symbol for any value between -11% to -50% and 11% to 50%, and a red symbol for any value between -51% to -100% and 51% to 100%. How can I do this? This is a problem I encountered when I tried to add a new rule for the negative numbers range through the conditional formatting rules manager, I received this message: "One or more of th...
How to apply budget for earned value analysis
I'm having huge problems finishing a plan I'm working on. Maybe someone here has some clever ideas. To take it from the beginning: This is a part of some school work, and I have a number of tasks, and for each of the tasks I have details on what the budget is, and the planned duration. At a specific point in time I have details on the completion rate of each task, as well as the money spent, and the actual duration. From these figures, I want MS Project to calculate figures such as BCWS, ACWP, BCWP, EAC, SPI and CPI. My problem is that I have not found a place to enter th...
Maintaining Number Formatting when merging from Access
Hi - I am merging from a pre-existing Access database into Word (both v2007) and noted that the currency formatting applied in the Access dbase is not being carried over into the Word document (currency sign, # of decimal places, etc.). Any ideas on how to solve this problem? Thanks, Chris Hi Chris, To control number & currency formatting in Word, add a numeric picture switch to the mergefield. To do this: .. select the field; .. press Shift-F9 to reveal the field coding. It should look something like {MERGEFIELD MyData}; .. edit the field so that you get {MERGEFIELD MyD...
Default value for Combo box on a Subform
I am having difficulty using VBA to set a value for a combo box control that resides on a subform based on the value in a text box control on a parent form. I know how to do this for textbox controls but Access 2003 does not seem to allow me to do this with a combo box using the same method. It returns the error "You can't assign a value to this object". So how do I assign a value to a combo box then? Below is my code: Private Sub Form_Open(Cancel As Integer) Dim strtable As String Dim strtable2 As String Dim getvenueid As Integer Dim sql As String Dim sql2 As String st...
default value with phone numbers
Hi, I would like to know if its possible to set a default value in the standard phone number field(s). To have access to numbers when calling from abroad, one needs to put +31 in front of the phone numbers. So the number 045 xxx xx xx would become +31 45 xxx xx xx. My question, is it possible to set this +31 as default value, so only the 45 xxx xx xx has to be types during input of phone numbers? Thanks Hi. Yes, there is the way. =) Take a look into SDK. There is are good examples of client-side customization. For Your question: in form constructor You must enter properties of main ...
go to cell with date equaling TODAY()
Hello! I have a spreadsheet with January 01, 2006 thru December 31, 2006 in ascending order in column A, each date in a different cell (A1, A2, A3, etc.). I don't enter data into this sheet everyday..in fact, months could go by before having to enter an occurance for say, March 31. Is there a way to have excel, upon opening the spreadsheet, advance the cursor to the cell with that day's date in it? -- Thank you all for your help! Using function Date rather than Today() Works for me. Private Sub Workbook_Open() Dim r As Long Dim T As Long T = Date r = Application.Matc...
This works except when both cells are blank.
Every once in a blue moon I get to work on a personal project. This is for track of some scores. I modified the formula from some code in the ng archives but haven't figured out how to take care of when both cells are blank. In E2: =IF($C2>$D2,"YOU WON!!",IF($C2<$D2,"You Lost ... :o(","Tied!")) this works great unless both C2 and D2 are blank when I get "Tied!" whereas should be blank. How do we accommodate for blank cells in the above formula? Thanks! :oD =if(count($c2:$d2)<2,"not enough scores", ... You could use =count...
How do you use "Block Formatting" for column headings?
I want to use it to Left Justify/set up on large spreadsheet, so that the second word falls right under the first. I am having trouble. Try this: Enter the first word, hold down the ALT key and press ENTER. Th cursor will move to the next line in the cell. Release the ALT key. Now enter the second word. Simply press ENTER. One cell, two lines. Use Format>Cells to make sure it is Left Aligned. HTH Bruc -- swatsp0 ----------------------------------------------------------------------- swatsp0p's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1510 View...
I need to shift cells down only to a point w/o disturbing the bot.
I have a spreadsheet in which the cells need to be moved down, however only to a point. If I move everything down it disturbs the bottom row of totals. Can anyone help me? I think you need to provide a few more details about what exactly you are trying to accomplish. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "wade2753" <wade2753@discussions.microsoft.com> wrote in message news:D905FEE5-2EA7-47A1-A532-4D0CA743121C@microsoft.com... >I have a spreadsheet in which the cells need to be moved down, >however o...
Time format 1500 become 15:00
Pls help me. I want to tape 1500 on A1 and A1 edit 15:00 Thanks for your answer Oh Yes, thanks so much. "מיכאל (מיקי) אבידן" wrote: > Use 'Custom Format': ##\:## > Micky > > > "tran1728" wrote: > > > Pls help me. > > I want to tape 1500 on A1 and A1 edit 15:00 > > > > Thanks for your answer > > Dear Micky, thanks again, I have a other question, si possible for Macro, The cursor go directy to the last row+1 of column A thanks for your reply "מיכאל (מיקי) אבידן" wro...
Money SDK for QIF format converter?
The brokerage that administers our company 401K downloads transaction data in the QIF format only. They only support downloading all transactions without specfying a date. So being an angry developer I started writing a conversion program in C# to convert the QIF into OFX and to automatically track and eliminate duplicate data. I have the QIF format and the OFX spec but I was wondering if Money has an SDK anywhere that would speed up the process for me. I have an MSDN Universal subscription but did not see anything in there. Thanks See http://umpmfaq.info/faqdb.php?q=10. "A...
Convert Positive Value to Negative Value
How can I get the "Amount" to be a negative number when the "Code" = "G6"? Maybe I could multiply by -1 and use the CASE statement, but I don't know how. Table Name = EncounterData Amount Code $15.00 G2 $13.00 G2 $18.00 G6 $16.00 G2 $25.00 G6 $30.00 G6 -- Tyro from Missouri You're right, use a CASE, for example Select Case When Code = 'G6' Then -1 * Amount Else Amount End As Amount, Code From EncounterData; Tom "Tyro" <Tyro@discussions.microsoft.com> wrote in message news:20959474-72BF-454D-8820-...
Error: You tried to assign a null value...
Hi Folks! I'm having trouble trying to figure out the error "You tried to assign a null value to a variable that is not a variant type." I have a NotInList event in a combobox (LastName_cbo) of a subform (sfrAttendee) on a form (frmAttendance). A message box pops open when a new name is entered into the combobox. Clicking "yes" opens a new form; clicking "no" generates the error. What I want to happen when I click "no" is for the field to be returned to its "clean" state with nothing in it, so that my user can start fresh...
Adding a rectangle to a table cell
I would like to add a rectangle to a table cell. However, when I try to do so I receive an error message stating the "Object does not support this action". Could someone please provide me with an example of: Adding a rectangle to a table cell Adding some text to the rectangle Setting the Back and Fore color of the rectangle Thanks to all in advance and have a Happy New Year! Brian For all intents and purposes, a table cell IS a rectangle. You can add text directly to it, set its color and so on. Depending on what you're after, you could add a rectangle atop...
Time formats
I am creating a template which requires the user enter a time (not present time, flight times), for example the user will be listing a number of flight arrivals and departures and what I want to do is have the user simply enter something like 804p and have the cell display it as 8:04 pm. I have looked at some custom formats and nothing seems to be able to do this. Any help would be appreciated. Thanks! Tracey ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ See http:...
Allow any Certain chr within a cell
Hi My question is in two parts:- 1. How do I restrict the contents of a cell a single chr namely "A to F" or 1-9" 2. Since the cell is only allowed to be 1 digit long when I have keyed and checked it moves on to the next cell automatically Thanks in advance Trev Hi Trever, Use cell validation: -- Part 1 of question Data (menu), validation allow: Text length Data: between Minimum: 1 Maximum 1 [ x] ignore blanks After you have tested with the above to see the error produced, would refine the error message by continuing with "Input Mess...
Looking for Datmax label format
Hi. Has anyone had success printing price labels with a Datamax printer? I have tried using the RMS Label Designer, but have not had much success. I'm able to query data and send it to the Bar Tender program via access, but that is very labor-intensive. Anyone have a working template I could look at? Failing that, any advice on how to develop my own template or find another way to print labels directly from RMS would be appreciated. I'm using a Datamax E-4203. Thanks Since no one answered my post, taking the bull by the horns, I opened up the label designer. Since I need priceta...
Changing values
I have a many-to-many relationship between Outings (with a primary key of EventID) and Members (with a primary key of MemberID). There is a table that ties the 2 together, tblAttendees that holds the EventID and MemberID. Recently, the MemberID of the tblAttendees changed on 2 records in the table. I can see the before in an earlier version of the database and can see what changed in the current database. It appears to be the first event in the tblAttendee which is changing when a new member is added. Am I doing something wrong or ?? Jeffrey Marks wrote: >I have a many-to-many ...