Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Replace Comma With Dot In TextBox

Hi again,
I want to set at my textbox,so that if user enter the number with comma instead of dot,the comma will automatically be changed into my default format(dot).for example,if user type in (86,5),the value will be converted to (86.5). I have read through the relevant previous thread regarding this matter and I wrote the function as below.It happens to be that (86,5) will change to (865.00),not as what it suppose to be.So can anyone correct my function below?I thank all of you in advance.


	VB:
	
 txtdiameter_Change() 
    If txtdiameter = vbNullString Then Exit Sub 
     
    If Not IsNumeric(txtdiameter) Then 
        MsgBox "Numbers Only" 
        txtdiameter = "86" 'default value'
    End If 
     
End Sub 
 
Private Sub txtdiameter_Exit(ByVal Cancel As MSForms.ReturnBoolean) 
    Dim temp 
    temp = txtdiameter.Text 
    With CreateObject("vbscript.regexp") 
        .Pattern = "[^0-9.]" ' pick up other than numbers/period
        .Global = True 
        temp = .Replace(temp, "") ' removes if any
    End With 
    txtdiameter.Text = Format(Val(temp), "#.00") 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
greet,

matt


Post your answer or comment

comments powered by Disqus
Hi Folks,

I have problem with data type.
I use data in worksheet from url location and all values which are updated come with
comma separator (polish decimal system use comma instead of dot as separator),
I use in my excel english decimal separator which is dot.
Now I would like to replace commas with dots but the problem is that data are constantly
updating so every time when that happen I have comma in updated data.

Can anyone advise or help me with macro or function please?

Is there any way I can use Find and Replace (or anything else) to replace commas with tabs? I want to use this on a data download (text file) of stock prices which looks like this:

AAC,20061221,1.84,1.85,1.83,1.85,82487
AAE,20061221,0.38,0.39,0.37,0.38,325573
AAI,20061221,0.00,0.00,0.00,0.00,0
AAM,20061221,0.21,0.21,0.20,0.20,241057
etc.

Will be grateful for any help.
Bungaree

Hi guys

I created a excel macro which opens a word document which is embedded in that excel workbook. i want to find and replace a word in a textbox(shape) in that word doc. for example..i want to replace "7XXX" with "7609" in that textbox in word.......by using word macro i found that textbox(shape) name is Text box 4........but i am not able to replace the text with word macro code in excel ....can u suggest me any alternative method....

thank u guys
Nandy

Hi All,

I have a list of names like so:

John Thomson, Paul Cooper, Ian Smith, Mark Devlin... and so on

The names are in one continuous string i.e. they start from one cell (A1).

I'd like to replace the commas with a carriage return to place each name
into its own cell. Is there function or calculation I can use to achieve
this?

Any advise would be appreciated.

TIA - H

Hi, I created a dynamic command button now I want to name the button from a variable. If the variable has spaces in how can I replace them with underscore.

Thank You

This is the code I used to create the button

Private Sub Create_Menu_Buttton_Click()

' Create object variable.
Dim VariableSheetName As String
Dim oleButton As OLEObject

VariableSheetName = InputBox("Enter name for new sheet to create.", "ENTER NEW PROJECT NAME", "")

'Insert rows to make space for new button
Rows("6:7").Select
Selection.Insert Shift:=xlDown

'Add the button to the sheet
Set oleButton = ActiveSheet.OLEObjects.Add _
(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=9, Top:=76.5, Width:=141, Height _
:=20)

'Rename the button, so that the code added later refers to it
oleButton.Name = VariableSheetName
oleButton.Object.Caption = VariableSheetName

End Sub

Hi all,

I'm doin a excel macro which is havin date function in it. Till now it is working fine with date in the format of mm/dd/yy

But i want the format of date as mm.dd.yy with dot in it.

How to do that?

Thanks in Advance.

Hello,

I was wondering if someone could help me. I would like to take two columns and put a succession of dots in the middle. Example
Column A:
Dan
Jeff
Mary
Jennifer
Column B:
555-2121
555-3164
555-4679
555-2364
Result:
Dan.........555-2121
Jeff..........555-3164
Mary........555-4679
Jennifer....555-2364

Any help would be appreciated

Thanks,
Randy

Ex. 2000
XP Pro SP3

Is there a way to replace nested IFs with the CHOOSE function so that I can overcome the seven nested IFs barrier in 2000?

I can replace IF with CHOOSE in a single IF formula which works, but I cannot see how to use 'nested' CHOOSE (if it is at all possible).

TIA

Zagra147

I am working with an 11000 row spreadsheet and all of the rows in one column have a comma where a decimal should be. For example 6,54 instead of 6.54. How can I replace the comma with the decimal without manually changing it in each row?

Also I have another column that has descriptions in each row. Some of the descriptions begin and or end with weird text. Like. $%@@&L. Is there a way I can highlight the entire row and only remove the strange characters?
thanks.

I have the following code for a checkbox on a form
Code:
Private Sub chkEnterActionsDelayPart_Click()
If chkEnterActionsDelayPart = True Then
    txtEnterActionsDeliverable = txtEnterActionsDeliverable & vbCrLf & _
        "Parts Delay"
Else
    txtEnterActionsDeliverable = _
              Replace(txtEnterActionsDeliverable, "Parts Delay", "")
End If
End Sub
when chkbox enabled the text is placed in textbox txtEnterActionsDeliverable
when chkbox disabled the text is removed from textbox txtEnterActionsDeliverable but retains all other text.

Question: How would I also remove the vbcrlf I placed in code for chkbox enabled?
Would all vbcrlf (Char 13?) be removed or can jut the one associated with the vbcrlf entered when checkbox enabled?

Thanks,
Chas

Hello

I have WS with over 100 text boxes. Many of them contain text "Current Program".
I would like to replace text "Current Program" in all these Textboxes with text "Testing".
How can I do it without opening each Textbox and replacing text manually?

Thank you
John

I am trying to replace all commas with a newline character. What do I put in
the Replace field?

Hellol everyone, I have being working ont this spread sheet for a long time because my work requires that I incert a number in a single cell in a new row. But some of the cells containt hundreds of numbers separated with a comma and this makes my life hell.

I would like to know if there is any way I can automatically replace the comma with a new row with the number in the cell.

I have attached it in TXT format.

If anybody can help me out please email me at spiderweb14@gmail.com. Thank you very much.

Is it possible to use the replace command to search a column for commas, and replace them with a return, so that the data falls on top of each other? There is no way to type in "alt-return" in the "replace with what" box in EXCEL.

Thanks for your consideration.

JD

Hi everyone
I am new in Excel vba programming I have a user form with two textbox and a command button. What I really wont to do is to enter A in textbox1 then B in textbox2 so when I click the command button to search sheet1 column B to find (A) and replace it with B in the same cell.
Please help I really need the code
Thank you
Gptechsxm

Hi all,

I have a worksheet that has a long list of ID numbers arranged in row 2.

I have a userform that lets the user enter comma delinated values in a textbox. The script on the OK button on this user form checks the validity of the entered IDs, and if valid, then removes that ID and associated records from the activesheet and other worksheets.

I've been asked to upgrade the functionality of this form so the user can do batch removals using ranged entries.

i.e. they want to be able to make an entry like "1, 3, 7, 10-25, 33, 45") and have all 21 of those values removed (and validity tested, of course).

Sounded trival to them, but I have no idea how to approach this at all. I've played with a few ideas but nothing has really worked.

My current code is as follows:

(userform textbox value replaced with simple activecell value.

To use:
1. place a series of integer values in column 1
2. place a series of comma delinated integer values in another cell
3. select that cell and fire the procedure)

Private Sub CommandButton1_Click()

    Dim I As Long
    Dim valueString As String
    Dim x As String
            
    valueString = ActiveCell.Value                  ' the thing to parse
    I = InStr(valueString, ",")                     ' find the first comma
    
    If (I = 0) Then                                 ' if no commas (single value)
        x = Trim(valueString)
        If IsNumeric(x) = False Then
            GoTo invalidInteger
            Exit Sub
        End If
        valueCheck (x)
    End If

    Do Until (valueString = "")
  
        If (I = 0) And (valueString <> "") Then     ' check in case our source does not end with a comma
            x = Trim(valueString)
            valueString = ""
        Else
            x = Trim(Left(valueString, I - 1))      ' get the latest value
            valueString = Mid(valueString, I + 1)   ' strip the value already gotten
        End If

        If IsNumeric(x) = False Then
            GoTo invalidInteger
            Exit Sub
        End If

        valueCheck (x)
        I = InStr(valueString, ",")                 ' find the next comma

    Loop
    
Call valuesOK
End

invalidInteger:
    MsgBox "Invalid Entry." & Chr$(13) _
            & x & " is not a valid numerical value." & Chr$(13) & Chr$(13) _
            & "Please enter only interger values."
    
End Sub

'----------------------------------------------------------------------------------------
'----------------------------------------------------------------------------------------

Private Sub valueCheck(ByVal x As String)

    Dim I As Integer
    Dim lRow As Integer
        lRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

    For I = 1 To lRow
        If ActiveSheet.Cells(I, 1).Value = x Then
            GoTo IsTrue
        End If
    Next I
    
    MsgBox "Invalid entry." & Chr$(13) _
            & x & " is not a valid ID number."
    End
               
IsTrue:

End Sub

'----------------------------------------------------------------------------------------
'----------------------------------------------------------------------------------------

Private Sub valuesOK()

    Dim I As Long
    Dim valueString As String
    Dim x As String
            
    valueString = ActiveCell.Value                  ' the thing to parse
    I = InStr(valueString, ",")                     ' find the first comma
    
    If (I = 0) Then                                 ' if no commas (single value)
        x = Trim(valueString)
        removeValue (x)
    End If

    Do Until (valueString = "")
  
        If (I = 0) And (valueString <> "") Then     ' check in case our source does not end with a comma
            x = Trim(valueString)
            valueString = ""
        Else
            x = Trim(Left(valueString, I - 1))      ' get the latest value
            valueString = Mid(valueString, I + 1)   ' strip the value already gotten
        End If

        removeValue (x)
        I = InStr(valueString, ",")                 ' find the next comma

    Loop


End Sub

'----------------------------------------------------------------------------------------
'----------------------------------------------------------------------------------------

Private Sub removeValue(ByVal x As String)
    
    Dim I As Integer
    Dim lRow As Integer
        lRow = ActiveSheet.Cells(Rows.Count, 1End(xlUp).Row

    For I = 5 To lRow
        If ActiveSheet.Cells(I, 1).Value = x Then
            ActiveSheet.Cells(I, 1).Value = ""
            ActiveSheet.Cells(I, 2).Value = ""
            ActiveSheet.Cells(I, 3).Value = ""
            GoTo NextValue
        End If
    Next I

NextValue:

End Sub
Any input on how this could be done, or input about tightening the code I have would be greatly appriciated!

--Ouka

I am trying to replace all commas with a newline character. What do I put in
the Replace field?

Hi.
I need a format to a comma (","), so it appears automatically in the Textbox.

but with the following criteria.

If you have a digit in Textbox
1 the result is 1

If you have two digits in Textbox
12 the result is 1,20

If you have three digits in Textbox
123 the result is 1,23

If you have four digits in Textbox
1234 will result 12,34

If you have five digits in Textbox
12 345 the result is 123,45

If you have six digits in Textbox
The result is 123456 123,456

If you have seven digits in Textbox
The result is 1234567 123,4567

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    TextBox1 = Format(TextBox1, "#"",""#")
End Sub

Private Sub TextBox1_Change()
    With TextBox1
        If Not IsNumeric(.Value) And .Value <> vbNullString Then
            MsgBox "only number"
            .Value = vbNullString
        End If
    End With
End Sub


I have a string in a cell which is essentially comma separated and want it in the same cell but with enters instead of commas.

Cell info to start with would look like this

Bob, The, Builder

And the result I am looking for is

Bob
The
Builder

All within the same cell so I can create a label which uses this information on a user form. I have attached a small example.

Thanks for the help.

I have been deleting rows of data based on the contents of various text boxes from the command menu- when comparing cells containing text with text in textboxes it performs perfectly.
However when I try to compare numerical values (max or minimum price etc,) it seems to always take the textbox contents as text so the comparison doesnt take place properly. Ive tried to use
Dim TextMax As integer etc
but it still doesnt do it....
Is there a property to the Text Box for setting the contents to integers?

Sample codes:

Worksheet code:

Private Sub TxtMin_Change()
Dim entry As Integer
entry = Sheets("Search").TxtMin.Value
SpMin.Value = TxtMin.Value
End Sub

Module Code:

If ActiveCell.Value < Sheets("Search").TxtMin.Value Then
Selection.EntireRow.Delete
ElseIf ActiveCell.Value > Sheets("Search").TxtMax.Value Then
Selection.EntireRow.Delete

Thanks.

Hi all,

i google it many times but i can't find what i'm looking for

In few words, I've got an excel files with many worksheets and i need to replace in the whole file a very long list of terms and phrases (up to 10.000 lines) that can include special characters (like " % / * etc ) and a phrase could be longer than 150 characters.

This list is saved in another files. The terms/phrases to find is in the column A and the terms/phrase to replace is in the column B. If a term in the A column has been found in the main document, the macro has to replace it with the term in the same line of the column B. If not, It has to skip to the next line.

The find must to be match exactly in the every cell, but the case sensitive is not necessary.

Optionally, I really appreciate to know if is possible to highlight the cell with a replaced term or phrase.

Let me know if you need more details...

Sorry for my English

Thanx

Hello,

I have a spreadsheet which acts as a student report generator (see attached).

Teachers select comments and a report is built up in cell P7. At the moment, I have a macro which copies the contents of P7 to another cell (P38) where a teacher can edit the report. Another macro can spellcheck the report in P38, and a final macro copies the edited, spellchecked report back to P7. That all works fine.

What I'd like is when the teacher is editing the report in P38, for the character counter to work "live", as at the moment, you have to edit the text in the formula bar (I think that is what it is called) and then press return for the character counter to get updated.

I have a feeling this is not possible when entering text into a cell in this way. Is this true? And if so, would it work if a textbox was used instead?

If it would, would someone be able to help me replace P38 with a textbox, and then edit my macros so that they perform the new functions in the textbox? That would be very kind!

1) I need the contents of P7 to be copied to the textbox which should be located where P38 is. That macro runs when the "click to edit report" button is pressed.
2) Ideally I'd then like the cursor to be ready for typing in that new textbox so the teacher doesn't have to click there themselves.
3) While the contents of the new textbox are being edited, I'd like there to be a cell which displays the character count of the textbox which updates LIVE. If the contents exceed 910 characters, I need there to be an error message, just as there is at the top of the page.
4) I'd like the spellcheck button to work with the new textbox.
5) I then need the contents of the textbox to be copied back to cell P7.

Any help is very welcome indeed, thank you for your time.

Nicholas

QUESTION: How do I check to see if a cell contains either "" or "/" and if it does then replace it with "-"?

THE REASON: I have a form which needs to be saved in a particular directory with the name "Form 1 - client name - matter name".

To do this I have put =("G:UsersCComplianceFile OpeningForm 1 - "&B7&" - "&D42) in Cell A1 and then in VBA added the following macro to a button which saves the file


	VB:
	
ThisFile = Range("A1").Value 
ActiveWorkbook.SaveAs Filename:=ThisFile 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This works ok BUT, if the user puts "/" or "" in either B7 or D42, it won't save and throws up an error because you can't use those signs when saving a file.

SO, please can someone help me with my dilemma - this is my first post so please be kind Thanks!

I’m trying to write a macro that will search a range to see if a cell has a value in it. and if it does copy that data to replace a specific term in Word.


	VB:
	
thisRw = 20 
For thisCol = 5 To 50 
    ws.Cells(thisRw, thisCol).Select 
    If myworksheet.Cells(thisRw, thisCol).Value > 0 Then 
        ‘ I need help With this part To export To Word 
    End If 
Next thisCol 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So if there is a value in the selected cell ,e.g F10 I want it to go to Word and:
* replace “number of people attending” with the value of F10.
* replace "on this date" with the value of F11

I have searched the forums but what I've found was people wanting to copy a whole worksheets, I just want specific cells.

Thanks


No luck finding an answer? You could always try Google.