Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combine 2 codes?(URGENT) (xls 97)

    hi i need to enter this code which formats any name entered to appear as (F. Last). However, i need to apply this code not directly in the applicable worksheet. I have a macro on a file called M.A.P. that opens the DUMMY file, inserts a column and makes the user enter a new name in the empty cell. This name is then updated using another macro back to the M.A.P. file. I would like to add this code to the first macro or second whichever is easier so that when a name gets entered it gets auoformatted by the code. I can not leave it in the DUMMY file because it constantly gets updated from access and becomes overwrited.
    SO how do i add the code below to my macro to make it work. The macro is at the bottom of this screen. Thanks alot.


    CODE TO FORMAT NAME

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, ActiveSheet.Range("A:A")) _
    Is Nothing Then
    Dim strCell As String
    Dim strRet As String
    Dim intMid As Integer
    strCell = Target.Value
    If Not IsNumeric(strCell) And strCell <> "" Then
    Application.EnableEvents = False
    intMid = InStr(1, strCell, " ", 1)
    strRet = Left(strCell, 1) & ". " & _
    Right(strCell, Len(strCell) - intMid)
    Target.Value = strRet
    Application.EnableEvents = True
    End If
    End If
    End Sub


    CODE FOR 1ST MACRO

    Sub AddNewName()
    '
    ' AddNewName Macro
    '

    ChDir "G:New Ideas"
    Workbooks.Open FileName:="svus3xsdm03auditNew IdeasMACRO.xls"
    Workbooks.Open FileName:="svus3xsdm03auditNew Ideasdummy.xls"
    Columns("A:A").EntireColumn.AutoFit
    Range("A2").Select
    Selection.Insert Shift:=xlDown
    Range("A2").Select
    Selection.Interior.ColorIndex = xlNone
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "Please enter New Name"""
    Range("B2").Select
    ActiveCell.FormulaR1C1 = _
    "Please enter ""New Name"" in blank cell A2 and Press button below"
    Range("B3").Select
    Application.CommandBars("Forms").Visible = True
    ActiveSheet.Buttons.Add(282, 42.75, 156.75, 51.75).Select
    Selection.OnAction = "MACRO.xls!BUTTON"
    ActiveSheet.Shapes("Button 1").Select
    Selection.Characters.Text = "Click After New Name Has " & Chr(10) & "Been Entered"
    With Selection.Characters(Start:=1, Length:=38).Font
    .Name = "MS Sans Serif"
    .FontStyle = "Regular"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = 49
    End With
    Range("B2:H2").Select
    Selection.Font.ColorIndex = 5
    With Selection.Interior
    .ColorIndex = 6
    .Pattern = xlSolid
    End With
    Range("A2").Select
    Application.CommandBars("Forms").Visible = False
    Range("A2").Select
    ActiveWorkbook.Save
    End Sub

    WHERE CAN I PASTE MY CODE TO, DO I HAVE TO EDIT THE CODE TO TELL IT WHICH FILE TO EFFECT???????????????????????

    THANKS,

    EDDIE

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Combine 2 codes?(URGENT) (xls 97)

    Eddie,

    I'm not sure that I follow what you are trying to do, but if you are looking to have a new name input and the formatted name place in cell A", may the following will work. It uses an Input Box to get the name and formats as F. Last, and places the result in A2. Is that any good ?

    Sub AddNewName()
    Dim strInpName As String
    Dim strNewName As String
    Dim intMid As Integer
    strInpName = Application.InputBox("Please Enter Name")
    intMid = InStr(1, strInpName, " ", 1)
    strNewName = Left(strInpName, 1) & ". " & _
    Right(strInpName, Len(strInpName) - intMid)
    If strInpName <> "" Then Range("A2").Value = strNewName
    End Sub

    Andrew C

  3. #3
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine 2 codes?(URGENT) (xls 97)

    Hey Andrew that's what I've been looking for. It allows me to overwrite a list validation based on your input. The only thing is I have 1 button assigned to the macro that contains the Input Box. When the info is entered it becomes pasted to cell A2. I know i can change cell A2 to have it append to wherever i want. How can I make it append to a specific cell that is either highlighted or slected by the user?? thanbks

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Combine 2 codes?(URGENT) (xls 97)

    You can replace <pre> If strInpName <> "" Then Range("A2").Value = strNewName</pre>

    with<pre> If strInpName <> "" Then ActiveCell.Value = strNewName</pre>

    and the cell that is selected will receive the input

    Andrew C

  5. #5
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine 2 codes?(URGENT) (xls 97)

    WORKS GREAT! tomorrow will be the last day of my internship so you may not see too many more of my questions. I greatly appreciate your help. ALL OF IT!!! thanks so much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •