Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Possible ActiveWorkbook.AcceptAllChanges/.Save Problem

  1. #1
    New Member
    Join Date
    Jul 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Possible ActiveWorkbook.AcceptAllChanges/.Save Problem

    Iím not sure if I have a problem here. Itís not bringing up an error code so it may be behaving as expected, Iím not entirely sure. I created two multipage forms after being encouraged to use them to fix one problem, I started with one from the getgo in this other project. It works perfectly except...

    Iím running a shared workbook, I know this is not ideal but donít have access to Access or other more robust software. Excel is as good as it gets. So I needed a way for it to check for the next blank row before inputting data since more than one user may be in it at any given time.

    So this is what Iím using:
    Private Sub Submit_Click()
    íAccept all changes and find last row
    ActiveWorkbook.AcceptAllChanges
    ActiveWorkbook.Save

    It seems to work, except that when I tested it with sharing turned on, but being the only one in it, it deposits my info on the second blank row. So for example

    Row1 - Header row
    Row2 - Blank
    Row3 - First record
    Row4 - Second record

    If I start with data already in the sheet it behaves similarly:
    Row1 - Header row
    Row2 - First record
    Row3 - Second record
    Row4 - Third record
    Row5 - Blank
    Row6 - New Data

    Without the above code, it puts the data in the first row after the header. Is this to be expected from the code Iím using? Will the above code do what I need it to do when itís more than just me in the spreadsheet? Is there a way to fix it?

    Thank you!

  2. #2
    Board Regular nemmi69's Avatar
    Join Date
    Mar 2012
    Posts
    287
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Possible ActiveWorkbook.AcceptAllChanges/.Save Problem

    are you using

    Code:
    LastRowNo = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    LastColNo = Worksheets("Sheet1").Cells(2, Columns.Count).End(xlToLeft).Column

  3. #3
    New Member
    Join Date
    Jul 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Possible ActiveWorkbook.AcceptAllChanges/.Save Problem

    Quote Originally Posted by nemmi69 View Post
    are you using

    Code:
    LastRowNo = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    LastColNo = Worksheets("Sheet1").Cells(2, Columns.Count).End(xlToLeft).Column
    Not currently, but I tried that earlier in the day and it resulted in an Error 1004?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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