dcsimg
Results 1 to 4 of 4

Thread: Need help to fix ''remove/add new added value next to its previous value''

  1. #1

    Thread Starter
    Member
    Join Date
    Jul 2014
    Posts
    53

    Need help to fix ''remove/add new added value next to its previous value''

    I have code below which help me to achieve is in the Column M we have a value, When ever user enter Value in 'M14:M1048576'. Previous value enter not remove instead of that add new value next to its previous value.

    But there is one issue in the code, i.e when you enter any new value -or- on any old values for example new empty cell M29 I enter value of 100 then you enter 99 again (or any other value) it work fine but you remove 99 and enter any other value instead it doesn't allow you to remove any old value. You have to remove the whole value if there is a Type and enter again from scratch.

    Is it possible fix below code which is problem, make the code more flexible to add and remove any typo or previous value without entering values from the very beginning.

    Best,

    Code:
    Dim M$ 
    Private Sub Worksheet_Change(ByVal Target As Range) 
        If Target.CountLarge = 1 And Target.Column = 13 And Target.Row > 13 Then
            If IsEmpty(Target) Then 
                M = "" 
            Else 
                Application.EnableEvents = False 
                Target.Formula = M & "+" & Target.Value2 
                Application.EnableEvents = True 
            End If 
        End If 
    End Sub 
    Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
        If Target.CountLarge = 1 Then If Target.Column = 13 Then If Target.Row > 13 Then _
        If Target.HasFormula Then M = Target.Formula Else M = "=" & Target.Value2
    End Sub

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,761

    Re: Need help to fix ''remove/add new added value next to its previous value''

    the only simple solution i can think of is if cell is not empty pop up a little userform with old value and empty box for new value, focus on empty box, but user can edit existing as well, you can incorporate update the cell and move to next cell on closing the form, user interaction would be about the same
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Member
    Join Date
    Jul 2014
    Posts
    53

    Re: Need help to fix ''remove/add new added value next to its previous value''

    Hi west,

    Thank you fro your reply.
    Can you please design a sample for us, let us give it a shot how much that pop up a little userform is helpful.

    Thank you again

    Best,

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,761

    Re: Need help to fix ''remove/add new added value next to its previous value''

    this works as expected
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Target.CountLarge = 1 And Target.Column = 13 And Target.Row > 13 Then
            If Not IsEmpty(Target) Then
                Application.EnableEvents = False
                With UserForm1
                    .Move Application.Left, Application.Top
                    .TextBox1 = Target.Value
                    .TextBox2.TabIndex = 0
                    .CommandButton1.TabIndex = 1
                    .Caption = Target.Address(False, False)
                    .Show
                    
                End With
                DoEvents
                Application.EnableEvents = True
            End If
        End If
    End Sub
    userform code
    Code:
    Private Sub CommandButton1_Enter()
        Range(Me.Caption) = TextBox1 + Val(TextBox2)
        Unload Me
    End Sub
    i had a minimal size userform with 2 textboxes and a commandbutton (all as named in the code)
    i had the commandbutton out of the visible area of the form, so just textboxes visible, you might prefer the command button to be visible
    pressing tab or enter from textbox2 fires the commandbutton
    reverse tab or mouse into textbox1 to edit the original value
    closing the userform with the red X is effectively cancel

    the only thing i would have preferred would have been to have the userform show directly over the target cell, but i did not find a solution to this, but i doubt it will be a serious problem, especially using the cell address as the caption for the form, and the userform having the focus, then returned to the target cell on form closing
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width