+ Reply to Thread
Results 1 to 12 of 12

VBA for userform to input into table

Hybrid View

  1. 11-25-2022, 01:42 PM #1
    k.tanton
    k.tanton is offline
    Registered User
    Join Date
    05-03-2019
    Location
    East Yorkshire
    MS-Off Ver
    365
    Posts
    69

    VBA for userform to input into table

    I have the same table I use on different sheets. Lets say column A is Name Column B is Surname. say on row 135

    I would like to open a userform on the dashboard. The first data to input would be the table/sheet then the same questions Name then surname. It then completes table, clears ready for the next data.

    I am very in experienced but have been at this for a week now and thought why have I not asked for help sooner.

    Please if anyone could help it would be apprecaited.

    Thanks in advance.
    Reply With QuoteRegister To Reply
  2. 11-25-2022, 02:24 PM #2
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,449

    Re: VBA for userform to input into table

    Hi k.tanton,

    Have you found the built-in Form... tool in Excel? I'd use it before trying to build my own userform.
    Look up Quick Access Toolbar (QAT) and "Form.." tool.
    https://www.ablebits.com/office-addi...20More%20items
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.
    Reply With QuoteRegister To Reply
  3. 11-25-2022, 03:52 PM #3
    k.tanton
    k.tanton is offline
    Registered User
    Join Date
    05-03-2019
    Location
    East Yorkshire
    MS-Off Ver
    365
    Posts
    69

    Re: VBA for userform to input into table

    Thank you..... yes i have seen this. I have columns in the table that have formulars in and dont wish to have them in the form.

    Any further help would be appreciated
    Reply With QuoteRegister To Reply
  4. 11-25-2022, 05:28 PM #4
    torachan
    torachan is offline
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,411

    Re: VBA for userform to input into table

    see big yellow banner - how to upload your workbook - get help relevant to your case.
    attached a simple demo form - needs very little alteration to accommodate more data.
    add a few extra columns to the table - alter a few variables in the code - keep the default names of textboxes and labels - let the code do the work.
    far better to have your formula controlled by the code - less sheet errors and less maintenance.
    Attached Files Attached Files
    Torachan,

    Mission statement; Promote the use of Tables, Outlaw the use of 'merged cells' and 'RowSource'.
    Reply With QuoteRegister To Reply
  5. 11-25-2022, 05:31 PM #5
    Logit
    Logit is offline
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    7,471

    Re: VBA for userform to input into table

    Create the headers for your table (Name, Surname, etc).

    On the userform create Textboxes that relate to the specific column headers (as seen in the code of the attached project).


    Private Sub btnOK_Click()
  Cells(Rows.Count, 4).End(xlUp).Offset(1).Resize(, 4) = Array(txtFirstName, txtSurname, "snb", "much simpler")
End Sub

REPLACES THIS MACRO :

Private Sub btnOK_Click()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim newRow As Long
    
    newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
    
    'The next two lines can be expanded as many times as needed for all the entry fields in your project
    
    ws.Cells(newRow, 1).Value = Me.txtFirstName.Value
    ws.Cells(newRow, 2).Value = Me.txtSurname.Value
    
End Sub
    Reply With QuoteRegister To Reply
  6. 11-26-2022, 04:47 AM #6
    Corbett
    Corbett is offline
    Registered User
    Join Date
    04-19-2021
    Location
    Dublin, Ireland
    MS-Off Ver
    O365, Win10 and Mac
    Posts
    47

    Re: VBA for userform to input into table

    If you're using a recent version of Excel then the attached add-in might do the trick. However I haven't tried it on Office 2007.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 11-26-2022, 07:28 AM #7
    k.tanton
    k.tanton is offline
    Registered User
    Join Date
    05-03-2019
    Location
    East Yorkshire
    MS-Off Ver
    365
    Posts
    69

    Re: VBA for userform to input into table

    Thank you everyone. I have the code that has taken me weeks to build for one sheet. (see below)
    I have multiple SHEETS with exactly the same information but for different departments. I would like to use the same one userform but add a combox at the top so the user can choose the SHEET the information is to end up on.... I hope this make sense. (the only difference in the information is the sheet in which it is going to go but I need the sheet to be variable)

    Private Sub Cancel_Click()

    Unload Data_UF

    End Sub


    Private Sub Combo_Trade_Change()

    Select Case Combo_Trade.Value
    Case Is = "Civils"
    Combo_Trade_Code.RowSource = "Civils"
    Case Is = "Electrical"
    Combo_Trade_Code.RowSource = "Electrical"
    Case Is = "Instruments"
    Combo_Trade_Code.RowSource = "Instruments"
    Case Is = "Mechanincal_Equipment"
    Combo_Trade_Code.RowSource = "Mechanincal_Equipment"
    Case Is = "Piping"
    Combo_Trade_Code.RowSource = "Piping"
    Case Is = "Rotating_Equipment"
    Combo_Trade_Code.RowSource = "Rotating_Equipment"
    Case Is = "Vendor"
    Combo_Trade_Code.RowSource = "Vendor"
    Case Is = "Sketches"
    Combo_Trade_Code.RowSource = "Sketches"
    Case Is = "Misc"
    Combo_Trade_Code.RowSource = "Misc"

    End Select
    End Sub

    Private Sub Label1_Click()
    Area_Code_Details.Show
    End Sub


    Private Sub Label13_Click()

    End Sub

    Private Sub Label23_Click()
    Area_Code_Details.Show
    End Sub

    Private Sub Label24_Click()
    Discipline_Code_Details.Show
    End Sub

    Private Sub Label3_Click()
    Discipline_Code_Details.Show
    End Sub


    Private Sub UF_NewDrwgNo_Continue_Click()
    'when we click the continue button

    'MsgBox "The UF will be closed", 0, "Message"

    Dim TargetRow As Integer
    Dim Counter As Integer
    Dim DrawingNo As String 'The full drawing Number
    Dim UserMessage As String

    Counter = Sheets("Engine").Range("B3").Value + 1001

    DrawingNo = Combo_Area & "-" & Combo_Trade_Code & "-" & Counter

    '(The below only if we cannot ensure a drawing number cannot be repeated- which it cannot)
    'If Application.WorksheetFunction.CountIf(Sheets("Drawing Register").Range("E5:E3000"), DrawingNo) > 0 Then
    'MsgBox "DRAWING NUMBER ALREADY EXISTS", 0, "check"
    'Exit Sub
    'End If

    If Sheets("Engine").Range("B4").Value = "New" Then

    TargetRow = Sheets("Engine").Range("B3").Value + 1
    UserMessage = DrawingNo & " was added to the Drawing Office Register"

    Else

    TargetRow = Sheets("Engine").Range("B5").Value
    UserMessage = DrawingNo & " was amended in the Drawing Office Register"

    End If

    Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 0).Value = TargetRow
    Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 14).Value = Txt_Project_No
    Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 15).Value = Txt_MOC_No
    Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 16).Value = Txt_WON
    Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 17).Value = Combo_Area
    Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 21).Value = Combo_Trade
    Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 18).Value = Combo_Trade_Code
    Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 6).Value = Txt_DrawnBy
    Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 7).Value = Txt_DrawnDate
    Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 19).Value = Combo_Scale
    Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 20).Value = Combo_Papersize
    Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 5).Value = Combo_Revisions
    Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 9).Value = Combo_DrwgStatus
    'Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 3).Value = Txt_DrawingNumber
    Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 4).Value = Txt_Drawing_Title
    Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 13).Value = Txt_Brief_Description
    Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 11).Value = Txt_VendorName
    Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 12).Value = Txt_Vendor_Drwg_No
    Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 10).Value = Txt_Historic_Drwg_No


    Unload Data_UF 'Closes userform

    MsgBox UserMessage, 0, "Complete"

    'MsgBox "Drawing " & DrawingNo & " was added to the Drawing Office Register", 0, "Complete"

    End Sub


    Private Sub UserForm_Click()
    ;
    End Sub
    Attached Files Attached Files
    Last edited by k.tanton; 11-26-2022 at 09:13 AM.
    Reply With QuoteRegister To Reply
  8. 11-26-2022, 08:43 AM #8
    torachan
    torachan is offline
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,411

    Re: VBA for userform to input into table

    see big yellow banner - how to upload your workbook - far easier then to give constructive in-context guidance.
    in the meantime please see forum rules (enclosing code between code tags) - this retains the structure and validation of the code - enables us to run without (hopefully) having to debug it.
    Reply With QuoteRegister To Reply
  9. 11-26-2022, 09:14 AM #9
    k.tanton
    k.tanton is offline
    Registered User
    Join Date
    05-03-2019
    Location
    East Yorkshire
    MS-Off Ver
    365
    Posts
    69

    Re: VBA for userform to input into table

    Thank you torachat. I hopefully have got this right.
    Reply With QuoteRegister To Reply
  10. 11-26-2022, 11:06 AM #10
    Corbett
    Corbett is offline
    Registered User
    Join Date
    04-19-2021
    Location
    Dublin, Ireland
    MS-Off Ver
    O365, Win10 and Mac
    Posts
    47

    Re: VBA for userform to input into table

    If I understand it correctly, the target sheet is determined by the Discipline (column W). Could you not just have one master sheet (Drawing Register), copy a blank copy of it into a new sheet and then in cell A23 enter the formula

    =FILTER( DrawingRegister!A23:Y68,DrawingRegister!W23:W68="Electrical")
    as I have done in the attached sample.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 11-26-2022, 11:14 AM #11
    torachan
    torachan is offline
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,411

    Re: VBA for userform to input into table

    code between tags should look like this.
    I will take a look through your file in a day or two (busy weekend)

    Private Sub Cancel_Click()

Unload Data_UF

End Sub


Private Sub Combo_Trade_Change()

Select Case Combo_Trade.Value
Case Is = "Civils"
Combo_Trade_Code.RowSource = "Civils"
Case Is = "Electrical"
Combo_Trade_Code.RowSource = "Electrical"
Case Is = "Instruments"
Combo_Trade_Code.RowSource = "Instruments"
Case Is = "Mechanincal_Equipment"
Combo_Trade_Code.RowSource = "Mechanincal_Equipment"
Case Is = "Piping"
Combo_Trade_Code.RowSource = "Piping"
Case Is = "Rotating_Equipment"
Combo_Trade_Code.RowSource = "Rotating_Equipment"
Case Is = "Vendor"
Combo_Trade_Code.RowSource = "Vendor"
Case Is = "Sketches"
Combo_Trade_Code.RowSource = "Sketches"
Case Is = "Misc"
Combo_Trade_Code.RowSource = "Misc"

End Select
End Sub

Private Sub Label1_Click()
Area_Code_Details.Show
End Sub


Private Sub Label13_Click()

End Sub

Private Sub Label23_Click()
Area_Code_Details.Show
End Sub

Private Sub Label24_Click()
Discipline_Code_Details.Show
End Sub

Private Sub Label3_Click()
Discipline_Code_Details.Show
End Sub


Private Sub UF_NewDrwgNo_Continue_Click()
'when we click the continue button

'MsgBox "The UF will be closed", 0, "Message"

Dim TargetRow As Integer
Dim Counter As Integer
Dim DrawingNo As String 'The full drawing Number
Dim UserMessage As String

Counter = Sheets("Engine").Range("B3").Value + 1001

DrawingNo = Combo_Area & "-" & Combo_Trade_Code & "-" & Counter

'(The below only if we cannot ensure a drawing number cannot be repeated- which it cannot)
'If Application.WorksheetFunction.CountIf(Sheets("Drawing Register").Range("E5:E3000"), DrawingNo) > 0 Then
'MsgBox "DRAWING NUMBER ALREADY EXISTS", 0, "check"
'Exit Sub
'End If

If Sheets("Engine").Range("B4").Value = "New" Then

TargetRow = Sheets("Engine").Range("B3").Value + 1
UserMessage = DrawingNo & " was added to the Drawing Office Register"

Else

TargetRow = Sheets("Engine").Range("B5").Value
UserMessage = DrawingNo & " was amended in the Drawing Office Register"

End If

Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 0).Value = TargetRow
Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 14).Value = Txt_Project_No
Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 15).Value = Txt_MOC_No
Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 16).Value = Txt_WON
Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 17).Value = Combo_Area
Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 21).Value = Combo_Trade
Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 18).Value = Combo_Trade_Code
Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 6).Value = Txt_DrawnBy
Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 7).Value = Txt_DrawnDate
Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 19).Value = Combo_Scale
Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 20).Value = Combo_Papersize
Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 5).Value = Combo_Revisions
Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 9).Value = Combo_DrwgStatus
'Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 3).Value = Txt_DrawingNumber
Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 4).Value = Txt_Drawing_Title
Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 13).Value = Txt_Brief_Description
Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 11).Value = Txt_VendorName
Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 12).Value = Txt_Vendor_Drwg_No
Sheets("DrawingRegister").Range("DrawingNo_Data_Start").Offset(TargetRow, 10).Value = Txt_Historic_Drwg_No


Unload Data_UF 'Closes userform

MsgBox UserMessage, 0, "Complete"

'MsgBox "Drawing " & DrawingNo & " was added to the Drawing Office Register", 0, "Complete"

End Sub


Private Sub UserForm_Click()
;
End Sub
    Reply With QuoteRegister To Reply
  12. 11-28-2022, 02:09 PM #12
    torachan
    torachan is offline
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,411

    Re: VBA for userform to input into table

    I have read through your query several times and I have to ask, "why", why do you want 10+ sheets all with the same data ?????
    or have I completely misunderstood your query.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Formulas in table not working with the input from userform.
    By crowmagnus in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-22-2021, 03:54 PM
  2. [SOLVED] Insert Table For input data into USERFORM
    By Rafaelpresa123 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 09-05-2020, 01:33 PM
  3. Vba-interactive userform to input/view/update data in table
    By lafleuk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-22-2016, 06:42 PM
  4. [SOLVED] Pass Value of combobox selected in Userform as input to textbox of other userform
    By Pradeepg in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-29-2013, 10:32 AM
  5. [SOLVED] Vlookup in UserForm - Get Input from listbox, Lookup in specific table from specific sheet
    By s2jrchoi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-30-2013, 09:00 AM
  6. Dynamic updating of current userform values based on userform data input or commandbutton
    By evancharles in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-28-2012, 06:09 PM
  7. Input Cell to create table with input # of rows
    By pgreenway in forum Excel General
    Replies: 3
    Last Post: 05-04-2011, 03:18 PM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1