Results 1 to 14 of 14
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Skip Cells On The Right... (2000 sr 1)

    I don't understand your question. Perhaps "skip" is not the word you need. Do you mean that you want to insert cells in columns T through X?

  2. #2
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Skip Cells On The Right... (2000 sr 1)

    (move or shift) entire set (T U V W) of cells to the right...

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Skip Cells On The Right... (2000 sr 1)

    The instruction to move cells in T2:X2 to the right is

    Range("T2:X2").Insert Shift:=xlToRight

    You will have to adapt this to use the row you're currently working on, for example:

    Dim lngRow As Long
    lngRow = ...
    Range("T" & lngRow & ":X" & lngRow).Insert Shift:=xlToRight

  4. #4
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Skip Cells On The Right... (2000 sr 1)

    lngRow = ...
    ????

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Skip Cells On The Right... (2000 sr 1)

    You must have code for your userform to keep track of the row (= record) you are working on. You must use that value.

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Skip Cells On The Right... (2000 sr 1)

    i Hans, i have maked many test with your suggestion but believe me, many crash...
    If you have a little time form me...

    I attache the code tath put various data in the cells...

    The controll is on txtbox38...

    The complete project as usual : http://www.gssitaly.com/l0785_def.zip

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Shift Set of Cells On The Right... (2000 sr 1)

    From a data entry of the userform i insert this value...
    My prob is:

    skip from the right all set of cells (T to X) if i insert in the txtbox4 a new value...

    In effect during the data antry if i insert "2" in the txtbox4 skip from the right all set of cells (T to X)

    In the wbook are present sheet ACTUALLY and AFTER

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Skip Cells On The Right... (2000 sr 1)

    By now, it has become almost impossible to follow what your code does, but I suspect that you must change

    If Not mScrolling Then

    Cells(ScrollBar1.Value + 6, 20) = Indirizzario.TextBox18.Value
    Cells(ScrollBar1.Value + 6, 23) = Indirizzario.TextBox38.Value
    Cells(ScrollBar1.Value + 6, 24) = Indirizzario.TextBox39.Value
    Cells(ScrollBar1.Value + 6, 22) = Indirizzario.TextBox35.Value
    Cells(ScrollBar1.Value + 6, 21) = Indirizzario.TextBox34.Value

    End If

    near the end of TextBox38_Change to

    If Not mScrolling Then
    Dim lngRow As Long
    lngRow = ScrollBar1.Value + 6
    Range("T" & lngRow & ":X" & lngRow).Insert Shift:=xlToRight

    Cells(ScrollBar1.Value + 6, 20) = Indirizzario.TextBox18.Value
    Cells(ScrollBar1.Value + 6, 23) = Indirizzario.TextBox38.Value
    Cells(ScrollBar1.Value + 6, 24) = Indirizzario.TextBox39.Value
    Cells(ScrollBar1.Value + 6, 22) = Indirizzario.TextBox35.Value
    Cells(ScrollBar1.Value + 6, 21) = Indirizzario.TextBox34.Value

    End If

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Skip Cells On The Right... (2000 sr 1)

    Hans naturally your code work correctlly, i have insert your code in the event change txtbox38
    but when i delete the esisting value in txtbox and insert new, your macro duplicate a blank range of cells moved and after recopy in the next range correctly...

    re download the projct i have make many correction important for this question...

    as usual here:

    http://www.gssitaly.com/l0785_def.zip

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Skip Cells On The Right... (2000 sr 1)

    The code to insert cells is not in the workbook in http://www.gssitaly.com/l0785_def.zip.

  11. #11
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Skip Cells On The Right... (2000 sr 1)

    I am make many test with your modify, but to dont corrupt the file i have delete your code and re upload original file...

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Skip Cells On The Right... (2000 sr 1)

    Does this solve the problem?

    If Not mScrolling Then
    Dim lngRow As Long
    lngRow = ScrollBar1.Value + 6
    If Not Trim(TextBox38) = "" Then
    Range("T" & lngRow & ":X" & lngRow).Insert Shift:=xlToRight
    End If
    Cells(ScrollBar1.Value + 6, 20) = Indirizzario.TextBox18.Value
    Cells(ScrollBar1.Value + 6, 23) = Indirizzario.TextBox38.Value
    Cells(ScrollBar1.Value + 6, 24) = Indirizzario.TextBox39.Value
    Cells(ScrollBar1.Value + 6, 22) = Indirizzario.TextBox35.Value
    Cells(ScrollBar1.Value + 6, 21) = Indirizzario.TextBox34.Value
    End If

  13. #13
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Skip Cells On The Right... (2000 sr 1)

    Tks Hans, your code work fine....
    But, after?

    In effect i would want to make an history for many input data in the txtbox (marked see the img)

    for example if is present in the range T to X a data and i reinsert new data from txtbox (marked see the img) store, (naturally with a link for the related record) the old data in a variable or in any side of workbookor. or make another strategy...

    In effect make for each record modified a zone of store data... old plus new...

    During the work i am not sure if my strategy is a good idea...

    If this workbook is a your work wath strategy your to solve thgis prob?...

    Tell me this si a big prob for me, i have worked on thgis wbook 2 month... but not have completed for this prob.
    As usual your comments and suggests are welcome.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Skip Cells On The Right... (2000 sr 1)

    Sal,

    I have never encountered the need to keep a history of changes in an Excel spreadsheet. If I did, I would probably switch to a database application.

    I have remarked before that adding "history" data to the right of existing data is limited by the number of columns in a worksheet; you replied that the number of modifications would be small. In that situation, the solution you have now is probably sufficient for your needs.

Posting Permissions

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