Next open row

JoeHelpexcel · Jul 22nd 2013

My users keep getting the same window "Resolve Conflicts" I know its related to the same row being populated with data i just dont know how to modify the VBA.. i need to except all data and have the entry occupy the next open row..thank you

Private Sub saveclose_Click()
Dim ssheet As Worksheet
Set ssheet = ThisWorkbook.Sheets("sheet1")
nr = ssheet.Cells(Rows.Count, 1).End(-4162).Row + 1
ssheet.Cells(nr, 1) = Me.TBDate
ssheet.Cells(nr, 2) = Me.CmbListItem
ssheet.Cells(nr, 3) = Me.TBUser
ssheet.Cells(nr, 4) = Me.ComboListItem2
ThisWorkbook.Save
ThisWorkbook.Close
Application.Quit
End Sub

patel · Jul 22nd 2013

Re: Next open row

Code

nr = ssheet.Cells(Rows.Count, "A").End(xlUp).Row  + 1

JoeHelpexcel · Jul 22nd 2013

Re: Next open row

Thank you.. but it seems to still only populate one entry.. myself and another user enter data simultaneously and when i reopen the workbook it only displays one record.. how can i have it display both entries?

HaHoBe · Jul 22nd 2013

Re: Next open row

Hi, patel,

Excel2013 shows -4162 as the equivalent of xlUp - any explanations from your expertise for a better understanding from my side?

Ciao,
Holger

JoeHelpexcel · Jul 22nd 2013

Re: Next open row

Sorry guys can someone please assist.. thank you

HaHoBe · Jul 22nd 2013

Re: Next open row

Hi, JoeHelpexcel,

the cod eyou posted seems to work fine -w emay only test with samples we created and will differ from you original workbook. Assuming the textboxes and ComboBoxes are filled you would need to elaborate if you want to close the workbook (then you can´t quit Excel as the macro stops right when the workbook is closed) or quit Excel as well.

Code

Private Sub saveclose_Click()
Dim nr As Long
With ThisWorkbook.Sheets("sheet1")
  nr = .Cells(Rows.Count, 1).End(-4162).Row + 1
  .Cells(nr, 1) = Me.TBDate
  .Cells(nr, 2) = Me.CmbListItem
  .Cells(nr, 3) = Me.TBUser
  .Cells(nr, 4) = Me.ComboListItem2
End With
ThisWorkbook.Save
''closes workbook with save but ends macro
'ThisWorkbook.Close SaveChanges:=True


''terminates instance of Excel
'Application.Quit
End Sub

Display More

Ciao,
Holger

JoeHelpexcel · Jul 22nd 2013

Re: Next open row

I need the combo box and text box entries to populate on the designated sheet and have the workbook close and save automatically for each user.. for some reason i cant get multiple entries to save because of a same row occupied error.. can you hlp?

HaHoBe · Jul 22nd 2013

Re: Next open row

Hi, JoeHelpexcel,

the code posted here should do okay for a single entry from each control - I don´t get the idea why you are talking about multiple entries being saved on the same row. Can you please elaborate or attach a sample workbook?

Ciao,
Holger

JoeHelpexcel · Jul 22nd 2013

Re: Next open row

Its a shared workbook where multiple users can enter data collected on sheet1.. for some reason that i dont understand, when 2 users enter data simultaneously only one user entry appears.. cant figure out how to attach the doc on this site

HaHoBe · Jul 23rd 2013

Re: Next open row

Hi, JoeHelpexcel,

maybe try using a boolean variable to determine whether a user has started to write data and disable the button for all other users for the time until the process is finished (any relational database will deliver this behaviour as a standard without the other disadvantages of a sheared workbook).

Ciao,
Holger

Next open row

Participate now!

Share

Users Viewing This Thread