Inputing data to Spreadsheet from Userform [Special Case] pls help me !

[lougs7]

Hey everyone,

[view Sample spreadsheet]Book2.xlsm

I have a spreadsheet that tracks details on Meetings (Date, Time, Attendance, Meeting Overview). I am trying to get a VBA code to input data from my UserForm to the proper place on my spreadsheet. There are a list of Furniture Supplier Companies, and every time a meeting happens with one of these companies the user of this spreadsheet with use the UserForm to input all the data and hit Input where it gets stored in the right place [haven't figured this out yet].

One of the Selections of the UserForm is the list of Furniture Supplier Companies. I am trying to get the VBA code to read the user selection for Supplier, and store the data in the under that Supplier in the Spreadsheet.... Not sure if this is possible since it is all in the same column. The structure of the page can change 100% to facilitate the logic of the code... Do you guys have any ideas how I can tackle this? Can someone kindly help me with the code to add the data?

I am really struggling here, anything would help me even if it's a simple suggestion.

Thanks VBA Express

[lougs7]

the top part of the spread sheet is just to keep a count on the total meetings, the data gets inputted below ideally

[SamT]

Here's the way I usually do it,

Module1 Code

Option Explicit

Public Enum Sheet1Columns
  EventCol = 1
  DateCol
  TimeCol
  FirmAttendenceCol
  CounterPartyAttendenceCol
  MeetingOverviewCol
End Enum

UserForm Code

Option Explicit

Private Sub cmdAdd_Click()
'See Module 1
Dim NextRow As Long
NextRow = InsertRow(cboSupplier)

With Sheets("Sheet1").Rows(NextRow)
  .Cells(EventCol) = txtEvent
  .Cells(DateCol) = txtDate
  .Cells(TimeCol) = cboTime
  .Cells(FirmAttendenceCol) = txtFirmAttendence 'verify
  .Cells(CounterPartyAttendenceCol) = txtCPAttendence
  .Cells(MeetingOverviewCol) = TextBox1
  End With

End Sub


Private Function InsertRow(SupplierName As String) As Long
'Inserts a new row at the bottom of a supplier block and returns that row number
Dim StartCel As Range
Dim NewRow As Long
Dim NamesCol As Range: Set NamesCol = Sheets("Sheet1").Range("A:A")

'Skip the top Total meetings listing
Set StartCel = NamesCol.Find("Event")

'Find the first empty row under the correct Supplier. Insert new row there
NewRow = NamesCol.Find(What:=SupplierName, After:=StartCel).End(xlDown).Row + 1
Sheets("Sheet1").Rows(NewRow).Insert
InsertRow = NewRow 'Return row number to calling sub
End Function

What is neat about this method is the Public Enum. Copy all the Headers on a sheet, then pasteSpecial(Values). Copy that and PasteSpecial Transpose. Copy that and Paste them into the Enum... End Enum and edit to make the header names valid Variable names. I can enter a couple of dozen Column number variables this way in just a minute. And Gee, I don't have to look to see if the control value is assigned to the right Column Number

Generic Enum variables start with the first variable = 0 unless otherwise noted, subsequent enum variables increment value by 1, unless otherwise noted.

If you have empty columns in your sheet, be sure to add an enum variable "EmptyCol1, 2, 3..." as a placeholder(s)

[lougs7]

Here's the way I usually do it,

Module1 Code

Option Explicit

Public Enum Sheet1Columns
  EventCol = 1
  DateCol
  TimeCol
  FirmAttendenceCol
  CounterPartyAttendenceCol
  MeetingOverviewCol
End Enum

UserForm Code

Option Explicit

Private Sub cmdAdd_Click()
'See Module 1
Dim NextRow As Long
NextRow = InsertRow(cboSupplier)

With Sheets("Sheet1").Rows(NextRow)
  .Cells(EventCol) = txtEvent
  .Cells(DateCol) = txtDate
  .Cells(TimeCol) = cboTime
  .Cells(FirmAttendenceCol) = txtFirmAttendence 'verify
  .Cells(CounterPartyAttendenceCol) = txtCPAttendence
  .Cells(MeetingOverviewCol) = TextBox1
  End With

End Sub


Private Function InsertRow(SupplierName As String) As Long
'Inserts a new row at the bottom of a supplier block and returns that row number
Dim StartCel As Range
Dim NewRow As Long
Dim NamesCol As Range: Set NamesCol = Sheets("Sheet1").Range("A:A")

'Skip the top Total meetings listing
Set StartCel = NamesCol.Find("Event")

'Find the first empty row under the correct Supplier. Insert new row there
NewRow = NamesCol.Find(What:=SupplierName, After:=StartCel).End(xlDown).Row + 1
Sheets("Sheet1").Rows(NewRow).Insert
InsertRow = NewRow 'Return row number to calling sub
End Function

What is neat about this method is the Public Enum. Copy all the Headers on a sheet, then pasteSpecial(Values). Copy that and PasteSpecial Transpose. Copy that and Paste them into the Enum... End Enum and edit to make the header names valid Variable names. I can enter a couple of dozen Column number variables this way in just a minute. And Gee, I don't have to look to see if the control value is assigned to the right Column Number

Generic Enum variables start with the first variable = 0 unless otherwise noted, subsequent enum variables increment value by 1, unless otherwise noted.

If you have empty columns in your sheet, be sure to add an enum variable "EmptyCol1, 2, 3..." as a placeholder(s)

Very nice of you for taking the time to help me. This is very nice.

I am getting a Compile Error - object Required at this line "Private Function InsertRow(SupplierName As String) As Long"

[lougs7]

I just saw your comments, going to work this out... Will give you a shout if I am still lost. Thanks so much