Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Programming Excel with VB

phadidonphadidon Member Posts: 16
I was finally able to make this work by using some info from you and an old VB for applications book. Here is the code that I used. Maybe you could critique my work. I do have a minor problem the 0's are not recognized when they are the first character in the string. How can I fix this. I am also trying to protect the sheet so the user can't make changes. I can do this but every time I run the macro it resets that property and the macro won't work anymore.

Private Sub cmdFormatNumber_Click()
'declare variables and assign address to Worksheet object variables
Dim strOldNumber As String, strNewNumber As String, lngPos1 As Long, lngPos2 As Long, lngPos3 As Long
Dim intPosOpen As Integer, intPosClose As Integer, intPosMinus As Integer, rngCell As Range

'Beginning in cell A2, undo the phone number format
Set rngCell = Range("a2")

Do Until rngCell.Value = ""
'Define the starting positions for Pos 1, 2, 3
intPosOpen = InStr(1, rngCell.Value, "(")
intPosClose = InStr(1, rngCell.Value, ")")
intPosMinus = InStr(1, rngCell.Value, "-")

'Strip the phone number format from the phone number and place an asterick at the end
OldNumber = rngCell.Value
lngPos1 = Mid(String:=rngCell.Value, Start:=intPosOpen + 1, Length:=3)
lngPos2 = Mid(String:=rngCell.Value, Start:=intPosClose + 2, Length:=3)
lngPos3 = Mid(String:=rngCell.Value, Start:=intPosMinus + 1, Length:=4)
'Concatenate the variables together with the asterik at the end
strNewNumber = lngPos1 & lngPos2 & lngPos3 & "*"

'Assign the NewNumber to the appropriate cell in column B
rngCell.Offset(columnoffset:=1).Value = strNewNumber

'Assign the address of the cell in the next row to the rngCell variable
Set rngCell = rngCell.Offset(rowoffset:=1)
Loop

End Sub


Thanks for all of your help,
Paul

Comments

  • BarkeeperBarkeeper Member Posts: 335
    : I was finally able to make this work by using some info from you and an old VB for applications book. Here is the code that I used. Maybe you could critique my work. I do have a minor problem the 0's are not recognized when they are the first character in the string. How can I fix this. I am also trying to protect the sheet so the user can't make changes. I can do this but every time I run the macro it resets that property and the macro won't work anymore.

    *snipp*

    I think, that it has to do with the datatype of the cells. Default Datatype of Cells is "Standard", so if a cell-value starts with a number, Excel automatically trims the value cutting off any leading 0's. Try to change the Datatype of the Cells to Text
    ------------------------------------------
    Only stupidity of mankind and the universe
    are infinite, but i'm not sure concerning
    the universe. A. Einstein

  • phadidonphadidon Member Posts: 16
    : : I was finally able to make this work by using some info from you and an old VB for applications book. Here is the code that I used. Maybe you could critique my work. I do have a minor problem the 0's are not recognized when they are the first character in the string. How can I fix this. I am also trying to protect the sheet so the user can't make changes. I can do this but every time I run the macro it resets that property and the macro won't work anymore.
    :
    : *snipp*
    :
    : I think, that it has to do with the datatype of the cells. Default Datatype of Cells is "Standard", so if a cell-value starts with a number, Excel automatically trims the value cutting off any leading 0's. Try to change the Datatype of the Cells to Text
    : ------------------------------------------
    : Only stupidity of mankind and the universe
    : are infinite, but i'm not sure concerning
    : the universe. A. Einstein
    :
    : Thank you, datatype was the problem. I tried to protect the sheet so that other users can't make changes but after I run the formatnumber macro one time it resets the locked property of the cells and won't run again without an error. Can you help me with this problem?
    Paul

  • BarkeeperBarkeeper Member Posts: 335
    : : : I was finally able to make this work by using some info from you and an old VB for applications book. Here is the code that I used. Maybe you could critique my work. I do have a minor problem the 0's are not recognized when they are the first character in the string. How can I fix this. I am also trying to protect the sheet so the user can't make changes. I can do this but every time I run the macro it resets that property and the macro won't work anymore.
    : :
    : : *snipp*
    : :
    : : I think, that it has to do with the datatype of the cells. Default Datatype of Cells is "Standard", so if a cell-value starts with a number, Excel automatically trims the value cutting off any leading 0's. Try to change the Datatype of the Cells to Text
    : : ------------------------------------------
    : : Only stupidity of mankind and the universe
    : : are infinite, but i'm not sure concerning
    : : the universe. A. Einstein
    : :
    : : Thank you, datatype was the problem. I tried to protect the sheet so that other users can't make changes but after I run the formatnumber macro one time it resets the locked property of the cells and won't run again without an error. Can you help me with this problem?
    : Paul
    :
    :

    You should at least provide me an Error-Number or Description which error occurs. VB-Coding in Excel isn't the stuff i normaly do
    ------------------------------------------
    Only stupidity of mankind and the universe
    are infinite, but i'm not sure concerning
    the universe. A. Einstein

  • phadidonphadidon Member Posts: 16
    I fixed the problem of protecting the sheet now I want to go a little bit further. I want to password protect the sheet. Right now I am turning the protection off at the begining of the number formatting loop and back on at the end of the loop. Can you help me with that?

  • nadkingcolenadkingcole Member Posts: 13
    I have done a lot of programming in VB using Excel.

    To find out how to do stuff I usually record a macro in Excel then look at the code for it to see how it's done in VB.
  • phadidonphadidon Member Posts: 16
    : I have done a lot of programming in VB using Excel.
    :
    : To find out how to do stuff I usually record a macro in Excel then look at the code for it to see how it's done in VB.
    :
    I appreciate the help, but I have tried that with no success. Thats actually how I figured out how to protect the sheet. Now I need to know how to password protect the sheet in VB code. Can anyone help?
  • BarkeeperBarkeeper Member Posts: 335
    : : I have done a lot of programming in VB using Excel.
    : :
    : : To find out how to do stuff I usually record a macro in Excel then look at the code for it to see how it's done in VB.
    : :
    : I appreciate the help, but I have tried that with no success. Thats actually how I figured out how to protect the sheet. Now I need to know how to password protect the sheet in VB code. Can anyone help?
    :

    Look at the VB-Help in Excel for the Protect-Method of the Worksheet-Object. Might be, what you are looking for.
    ------------------------------------------
    Only stupidity of mankind and the universe
    are infinite, but i'm not sure concerning
    the universe. A. Einstein

  • Andre YoungAndre Young USAMember Posts: 0

    _____ \ http://forcoder.org \ free video tutorials and ebooks about { PHP, Assembly, Go, Python, PL/SQL, Visual Basic .NET, JavaScript, Scratch, Ruby, Visual Basic, Objective-C, Swift, C#, Java, Delphi, R, C++, C, Perl, MATLAB Ada, VBScript, Logo, SAS, Clojure, Lisp, Erlang, Rust, Dart, FoxPro, Julia, Scala, Fortran, Apex, Prolog, LabVIEW, COBOL, F#, Crystal, ML, Alice, Lua, Transact-SQL, Bash, Hack, ABAP, D, Scheme, Kotlin, Awk } ___

Sign In or Register to comment.