Negative to zero code

I went in and formatted a cell to: 0;"0";0 so that all negative numbers will 
be converted to zero. I have a textbox on a userform that I need to do the 
same thing. What code would I use? I tried this but it doesn't work:

txtRemain.Value = Format(txtRemain.Value, "#,#00;""0""")

Any help would be greatly appriciated.

Thanks,
~Gabe


0
Utf
5/13/2010 10:35:01 PM
excel.programming 6508 articles. 2 followers. Follow

5 Replies
799 Views

Similar Articles

[PageSpeed] 43

This worked for me:

Sub gh()
    Sheets(1).TextBox1.Text = 123.751
    MsgBox "OK"
    Sheets(1).TextBox1.Text = _
    Format(Sheet1.TextBox1.Text, "#,##0.00")
End Sub


"Gabe" <Gabe@discussions.microsoft.com> wrote in message 
news:0FA5E95F-1BDB-4F0A-B606-4E0D1B7DB7DA@microsoft.com...
>I went in and formatted a cell to: 0;"0";0 so that all negative numbers 
>will
> be converted to zero. I have a textbox on a userform that I need to do the
> same thing. What code would I use? I tried this but it doesn't work:
>
> txtRemain.Value = Format(txtRemain.Value, "#,#00;""0""")
>
> Any help would be greatly appriciated.
>
> Thanks,
> ~Gabe
>
> 


0
JLGWhiz
5/13/2010 11:33:51 PM
Disregard, I misread the posting.


"JLGWhiz" <JLGWhiz@cfl.rr.com> wrote in message 
news:uCzF5Sv8KHA.4600@TK2MSFTNGP02.phx.gbl...
> This worked for me:
>
> Sub gh()
>    Sheets(1).TextBox1.Text = 123.751
>    MsgBox "OK"
>    Sheets(1).TextBox1.Text = _
>    Format(Sheet1.TextBox1.Text, "#,##0.00")
> End Sub
>
>
> "Gabe" <Gabe@discussions.microsoft.com> wrote in message 
> news:0FA5E95F-1BDB-4F0A-B606-4E0D1B7DB7DA@microsoft.com...
>>I went in and formatted a cell to: 0;"0";0 so that all negative numbers 
>>will
>> be converted to zero. I have a textbox on a userform that I need to do 
>> the
>> same thing. What code would I use? I tried this but it doesn't work:
>>
>> txtRemain.Value = Format(txtRemain.Value, "#,#00;""0""")
>>
>> Any help would be greatly appriciated.
>>
>> Thanks,
>> ~Gabe
>>
>>
>
> 


0
JLGWhiz
5/14/2010 12:34:04 AM
I think you are going to have a hard time getting the TextBox entry to 
format as a number.  It only deals with text.  So any number formats have to 
be handled either on the worksheet or in the variable container prior to 
posting to the text box.  Once in the text box, it is text and while it can 
be formatted to look like numbers, it is not numeric, so it cannot tell if a 
value is negative.


"Gabe" <Gabe@discussions.microsoft.com> wrote in message 
news:0FA5E95F-1BDB-4F0A-B606-4E0D1B7DB7DA@microsoft.com...
>I went in and formatted a cell to: 0;"0";0 so that all negative numbers 
>will
> be converted to zero. I have a textbox on a userform that I need to do the
> same thing. What code would I use? I tried this but it doesn't work:
>
> txtRemain.Value = Format(txtRemain.Value, "#,#00;""0""")
>
> Any help would be greatly appriciated.
>
> Thanks,
> ~Gabe
>
> 


0
JLGWhiz
5/14/2010 12:45:50 AM
Try this in the text box's _Change event:

Private Sub txtRemain_Change()
  If Val(txtRemain) < 0 Then
    txtRemain= 0
  End If
End Sub


"Gabe" wrote:

> I went in and formatted a cell to: 0;"0";0 so that all negative numbers will 
> be converted to zero. I have a textbox on a userform that I need to do the 
> same thing. What code would I use? I tried this but it doesn't work:
> 
> txtRemain.Value = Format(txtRemain.Value, "#,#00;""0""")
> 
> Any help would be greatly appriciated.
> 
> Thanks,
> ~Gabe
> 
> 
0
Utf
5/14/2010 1:21:01 AM
Private Sub TextBox1_Exit _
(ByVal Cancel As MSForms.ReturnBoolean)
    If IsNumeric(TextBox1) Then
        If TextBox1.Value < 0 Then
        TextBox1 = 0
        End If
    End If
End Sub



-- 
Regards
Dave Hawley
www.ozgrid.com
"Gabe" <Gabe@discussions.microsoft.com> wrote in message 
news:0FA5E95F-1BDB-4F0A-B606-4E0D1B7DB7DA@microsoft.com...
>I went in and formatted a cell to: 0;"0";0 so that all negative numbers 
>will
> be converted to zero. I have a textbox on a userform that I need to do the
> same thing. What code would I use? I tried this but it doesn't work:
>
> txtRemain.Value = Format(txtRemain.Value, "#,#00;""0""")
>
> Any help would be greatly appriciated.
>
> Thanks,
> ~Gabe
>
> 

0
ozgrid
5/14/2010 3:18:51 AM
Reply:

Similar Artilces:

Formating Negative Figures in Brackets
Excel 2007 In earlier version of Excel, we could get a specific format for presenting the negative numbers in brackets and / or in red color. I could not find it in the Excel 2007. This can be done in the Custom Format. I do not have specifics for it. Can you give me the format for the same ? Why did the Excel remove this useful and oft needed feature from their new version, or am I missing something ? -- Shrikant The below might be useful http://support.microsoft.com/kb/832285 -- Jacob "Shrikant" wrote: > Excel 2007 > In earlier version of Exc...

RMA Reason Codes
Is there a way to delete RMA reason codes from the setup list without having to go into Query Analyzer? Can you not go to the RMA Reason Code Maintenance screen and delete it there? Cards >> Returns Management >> RMA Reason Code "Elaine" wrote: > Is there a way to delete RMA reason codes from the setup list without having > to go into Query Analyzer? Steve, Well, yes, I can. That works. Sorry - I'm new to the RMA process. Thanks for your help! "Steve Chapman" wrote: > Can you not go to the RMA Reason Code Maintenance screen and del...

excel truncating leading zeros
Hi, I am trying to use the import from text file into an excel sheet. I read many queries and figured out how to import the values as text without dropping the leading zeros. Please suggest a way where i can import all the values as "text" type. I want to avoid creating an array as the size of my text file varies from time to time. So i will not be sure how big the array must be. Please suggest. Thanks . regards, veena. In Step 3 on the Text Import Wizzard, select "text" in the upper righ hand corner of the screen -- ElsiePO ------------------------------------------...

Min not zero revisited
I am trying to input an array function onto my Excel sheet through VBA. I try the following code, and instead of giving me some type of error... it just skips past the rest of my code and goes straight to "End Sub" after trying to evaluate. Can someone please tell me two things... 1) what did I do wrong? and 2) why would it skip all the rest of my code and go straight to End Sub? Thanks! Matt LastRow = Cells(Rows.Count, "F").End(xlUp).Row ActiveCell.Offset(3, 0).FormulaArray = "=MIN(IF(R13C:R" & LastRow & "C)>0,R13C:R&...

Negative Allocation
I was attempting to post a batch that contained a transfer. The post failed and I recevied a message that "The serial number does not exist for this item and site". The items were moved from the source site to the destination, but the allcoated quantites in the source site are now negative. Any ideas how to fix this? Have you run the inventory reconcile process? (Utilities - Inventory - Reconcile) "Eric Capponi" wrote: > I was attempting to post a batch that contained a transfer. The post failed > and I recevied a message that "The serial number does not...

Subform Linking via Code
Hello All, I am trying to change the child/master links for a subform at the click of a button. The reason for this is to switch between current and completed projects in the subform. There is already a filter on the parent for which proved too difficult to amend. My problem occurs when I click on the button. I get a warning that tells me that the master and child properties must contain the same number of fields. Is there a way to bypass this warning so that the rest of my script can run? Here is the code I have so far: Private Sub Toggle_Current_Complete_Click() Me.PMInterfaceSub.Li...

making a negative effect
I am trying to switch the black and white color of a tempalte for an invitation from black background white lettering to opposite. any help would be appreciated dctakj <dctakj@discussions.microsoft.com> was very recently heard to utter: > I am trying to switch the black and white color of a tempalte for an > invitation from black background white lettering to opposite. any > help would be appreciated Printing this on white paper will be expensive because of the amount of ink used. Printing this in black paper will be (virtually) impossible. Now that's under your belt,...

HQid is zero at HQ and Store
We have been having a lot of reporting problems lately and I found out that the HqId at the store levels are 0. Also there are items in the HQ database that also have a HQId of 0. We use Retail Realm Matrix Creator and items that are created automatically have an HQId of 0. I need to update the item table at the stores but I need to make sure all items at HQ have a HQID. Can I assign martixed items at HQ with HQIds without having to create all the items again? I'm sure all I have to do is update the item table at the store level to correct everything once I know that all the item...

Error in AVERAGE(IF...) formula; involves Zero Value and Blank Cells
Hi there, I am getting an error in this formula: =AVERAGE(IF(($Y13:AH13<>0)*($Y$10:AH$10<>0),$Y13:AH13/$Y$10:AH $10))*100 Excel states that a value used in the formula is of the wrong data type, and the calculation steps underlines the first ($Y13:AH13 and results in #Value!<>0. The long-hand way of typing it is (up to column AG): =(((Y13/Y$10)+(Z13/Z$10)+(AA13/AA$10)+(AB13/AB$10)+(AC13/AC$10)+(AD13/ AD$10)+(AE13/AE$10)+(AF13/AF$10)+(AG13/AG$10))*100)/ COUNT(Y13,Z13,AA13,AB13,AC13,AD13,AE13,AF13,AG13) The data values, which are student scores on assignments, are currently ...

Negative Expenses
I'm using Money 2007 Premium but have had this problem in the last several versions as well. Why is it that if I record a negative expensive, it is not reflected in the reports? An example: Let's say I go out to dinner with a friend. We receive the check and I pay by credit card and my friend pays me with cash to cover his/her part. I record the restaurant in the Food: Dining Out category in my credit card account and then record that I received the cash in my Cash account and classify it as a negative expense to the same category. However, when I view the reports, only the pos...

Zip code loses leading zero Mail Merge from Excel to Word Labels
Even though formatted as zip codes, I cannot get the leading zero to show in my zip codes when using Word mail merge to create address labels. Example: 04103 shows up on the label as 4103 Debra Dalgleish posted this to a similar question: There's an article on the Microsoft web site that might help you: Answer Box: Numbers don't merge right in Word http://office.microsoft.com/en-ca/assistance/HA011164951033.aspx And if you prefer the old Mail Merge helper, Word MVP Suzanne Barnhill has instructions here: http://home.earthlink.net/~wordfaqs/CustomizingWord2002.htm abou...

Negative Zero?
Sometimes when I subtract a number from itself, I get a negative zero, rather than a simple zero. Why is this? probably a small diff somewhere in the 15 digits of the number -- Don Guillett SalesAid Software donaldb@281.com "Herbert" <Herbert@discussions.microsoft.com> wrote in message news:9517F5D9-3A15-4A97-93E1-B442A948E3E0@microsoft.com... > Sometimes when I subtract a number from itself, I get a negative zero, rather > than a simple zero. Why is this? It means that your result is less than zero, but not within the precision you are displaying. For example, ...

Determine the number of bits in a negative number
I'd like to determine the number of bits (the position of the highest on bit) in a negative number (say -1) without using knowledge of its type. Valid operations would be AND, OR, XOR, negation, and one's compliment can be done as: 'Get a -1 of the same type as myvar Dim minus1: minus1 = myvar - (myvar + 1) Dim onesComp: onesComp = myvar XOR minus1 You could also use multiplication or division by 2 or other mathematical operators/functions, but then sign extension comes into play. The standard solution is to presume the answer given the type of the variable, but...

Code Event Criteria
All, how can, with the code below, have the f![Location] = Filename line to only run if everything else occured? Function SaveAppItem() Set f = Screen.ActiveForm surl = f![Hyperlink] Filename = "\\Bcp\bcp\cmcc\cmcdb\20080922_CMCDB2\Documents\CE\Appendix\" & f![UpdateNum] & ".pdf" 'With CreateObject("Wscript.Shell") 'Shell ("explorer.exe Filename") Sleep (3000) Dim oIExplorer: Set oIExplorer = CreateObject("InternetExplorer.Application") oIExplorer.Navigate surl oIExplorer.Visible = True Sleep (3000) Do: ...

Quote product line should allow for negative extended amounts.
Quote product lines should allow for negative amounts. A situation where this is required is when dealing with bulk discounts. We want the ability to apply a product line with a negative value and have the overall quote value reflect this change. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agre...

Excel Formula Negative/Negative =+%
I am trying to write a formula to correct negative %..... Example....-500 2005 -400 for 2004 Variance = -100 Variance % is showing 25% should be (-)25%......How to write formula? "BigDon" <BigDon@discussions.microsoft.com> wrote in message news:B10D8375-FCE5-49A9-9A5D-3DD2C39C5868@microsoft.com... > > I am trying to write a formula to correct negative %..... > Example....-500 2005 -400 for 2004 Variance = -100 > > Variance % is showing 25% should be (-)25%......How to write > formula? > Hi, First, I would suggest considering whether you actually want ...

negative numbers in smartlists
When generating my items usage in a smartlist the quantity comes up xxxx-. Excel does not recognize this field as a number. Consequently, I can't do a calculation on this field in Excel. Is this a formatting that can be changed up front in GP? If not, is there an easy way to reformat the fields in Excel? Ok -- I have discovered that on at least 1 other person's workstation the number comes in as a number when running this smartlist. A GP formatting question? ...

Native XML Support for Non-Managed C++ Code
Since XML is becoming so important in main stream programming, is there any possiblity that Microsoft would add native XML support to it's non-managed VC++ 7.1 development environment. It would be nice to have native MFC classes to access and manipulate XML similar to the functionality that is already available in .Net. It is a real pain for C++ programmers to use smart-pointers, VARIANTS, and/or building class wrappers for COM components just to be able to access this core programming functionality. Thanks, Bruce COM is now almost everything for Microsoft now, every things comes in ...

Using the code behind to show an image
How do you made a reference to an image in the code behind? I'm trying to show a high priority "!" image if an "h" was in the label. Please the code below. view plaincopy to clipboardprint? private void ShowPriority() { StorePriority.Value = "n"; myLabel.Text = StorePriority.Value; if(myLabel.Equal("n"); { myLabel.Text = " "; } else { // How do reference the path to the image? } You have to use an asp:Image control, with ImageUrl property set to th...

Balances negative
I accepted a downloaded transaction today and Money 2004 crashed on me. When I restarted, I noticed that my checking account balance was negative and several unsent electronic billpay transactions had materialized. The only backup I had seems to not work (damn floppies). Ive tried both a quick and standard repair as well as deleting my IE temp files. Any ideas? I need help!!! I forgot to add that when I view all transactions in the register, everything looks fine. It's when I switch to View unreconciled transactions that the balance screws up. Tommy Becker wrote: > I ac...

HELP with Subform Coding
I have two unbound fields on a form that I use for Search boxes and two subforms forms linked to its corresponding Search box. "Search" and "SearchJ" and linked with Child Field (Number ID) and Master Field (Search) and Child Field (JNumberID) and Master Field (SearchJ). On the After Update Event of both Search boxes, the cursor moves to the respective subform anfd retrieves the correct record. I would like to ELIMINATE one of the subforms and USING CODE change the Child Fields and Master Fields based on which Search Box I am using. I tried using IF statements in t...

negative dates or times are displayed as # # # #
I have two cells that carry two different times as follow cell A1 has 00:05:00 cell A2 has 00:04:00 cell A3 should display the difference in negative way(-00:01:00). I only get ##### instead of the answer in cell A3 and a message reads (negative dates or times are displayed as # # # #) Your help will be much appricated. thanks That's what Excel does; it cannot work with negative time/dates You could try =ABS(A2-A1) and have a flag in an adjacent cell =IF(A2<A1,"Neg","Pos") best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email &quo...

smartlist negative number for returns
I am trying to create a smartlist that would return the soptype return and invoice but have the return amount appear as a negative number. I was able to return both doctypes with search type set to 'match one or more'. I am having trouble making the returns appear as negative amouts. Would I need smartlist builder? Yes. In SLB you can create a calculated field that will take the amount and multiply by -1. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at http://dynamicsgpblogster.b...

making a box create a code
I am having a hard time understanding the 'if' and 'and'; 'or'. I am trying to have a box on a spreadsheet that when it is'checked' creates a code on another page. This code would generate a dollar value. How do I go about doing this? -- Rick Wolfe Malaspina Coach Lines Try this anywhere on Sheet2 with X typed in A1 of Sheet1 and then with Z =IF(Sheet1!A1="X",25,IF(Sheet1!="Z",45,0)) then tell us what is wrong best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Rick Wolfe"...

Subscript out of range ... sometimes! (Same code unpredictable re
XL 2003 The file Test.txt is in memory but I want it to be the active sheet therefore I attempt to execute: .... Dim UpdateFileName as String .... UpdateFileName = "Test.xls" 'shown just as example Windows(UpdateFileName).Activate .... Sometimes I get "Subscript out of range" error other times not. Results of major frustrating investigation: I noted that when the filename only (i.e. Test.xls) in on an XL Tab on the (Win XP) task bar, all is OK. When the path is included with the filename on an XL Tab on the XP Task bar I get the error. Further, if I use th...