+ Reply to Thread
Results 1 to 15 of 15

UserForm populating Multiple Sheets

Hybrid View

  1. 11-01-2011, 06:33 AM #1
    Toonies
    Toonies is offline
    Forum Contributor Toonies's Avatar
    Join Date
    07-30-2009
    Location
    Newcastle, UK
    MS-Off Ver
    Excel 2016
    Posts
    511

    UserForm populating Multiple Sheets

    Hi and thanks for looking at my post.

    This is my 1st real attempt at putting together VBA coding so its may be wrong.

    Ok here goes

    I have made a UserForm that lets me populate a sheet based upon the

    1. Name
    2. Date
    3. Vacation Hours

    all chosen from ComboBoxes(couldn't quite do it with TextBoxes)

    It works fine on each individual sheet from seperate buttons & UserForms.

    What I'm looking to do is to do it all with 1 Userform for all the sheets.

    The sheets are named after each month so there will be 12

    heres a copy of the file I have only put 2 sheets in to hopefully make it easier

    Book abc.xls

    the VBA code is long so could it be shortened (I said I was New)

    I look forward to any help or replies

    Cheers

    Toonies
    Reply With QuoteRegister To Reply
  2. 11-01-2011, 09:53 AM #2
    incjourn
    incjourn is offline
    Registered User
    Join Date
    03-16-2004
    Location
    UK
    MS-Off Ver
    2003
    Posts
    85

    Re: Need Help with UserForm populating Multiple Sheets

    Try using the Me.Controls method to assign and get values from the userform e.g.

    Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub CommandButton3_Click()
    For Each ct In Me.Controls
        If Left(ct.Name, 3) = "Day" Then
            MsgBox ct.Name
        End If
    Next ct
End Sub

Private Sub MarchAdd_Click()
    Dim iRow As Long
    Dim lRw As Long
    Dim lCol(1000) As Long
    Dim ws As Worksheet
    Dim ws1 As Worksheet
    
    Application.ScreenUpdating = False
    Set ws = Worksheets("January")
    With ws
    lRw = Me.NameBox2.ListIndex + 6    '< Listindex starts at 0, day1 is on Row3 so add 4
        For Each ctl In Me.Controls
            If Left(ctl.Name, 3) = "Day" Then
                cNr = Mid(ctl.Name, 4) * 1
                lCol(cNr) = ctl.ListIndex + 3
            End If
        Next ctl
    End With
    
    Application.ScreenUpdating = False
    Set ws = Worksheets("January")
    With ws
        'find first empty row in database
        iRow = .Cells(.Rows.Count).End(xlUp).Offset(1, 0).Row
        For Each ctl In Me.Controls
            If Left(ctl.Name, 3) = "Hours" Then
                cNr = Mid(ctl.Name, 6) * 1
                .Cells(lRw, lCol(N)).Value = Left(Trim(ctl.Value), 10)
            End If
        Next ctl
        
        'copy the data to the database
       For Each ctl In Me.Controls
            If Left(ctl.Name, 3) = "Day" Then
                ws.Cells(iRow).Value = ctl.Value
            End If
       Next ctl
    End With
    
    Application.ScreenUpdating = True
    Unload Me
       
    End Sub


Private Sub UserForm_Initialize()
    Dim Dys As Integer
Dim N As Integer
Dim Dt As Date
Dys = DateValue("1/1/" & Year(Now)) - DateValue("1/1/" & (Year(Now) - 1))
ReDim Ray(1 To Dys)
Dt = "1/1/" & Year(Now)
    For N = 1 To Dys
        Ray(N) = IIf(N = 1, Dt, DateAdd("d", 1, Dt))
        Dt = Ray(N)
    Next N
    
    NameBox2.List = Application.Transpose(Range("Employees").Value)
    
    For Each ctl In Me.Controls
        If Left(ctl.Name, 3) = "Day" Then
            ctl.List = Application.Transpose(Ray)
        ElseIf Left(ctl.Name, 5) = "Hours" Then
            ctl.List = Range("HolidayHours").Value
        End If
    Next ctl
End Sub
    You will also need the 'Application.Worksheets'

    For Each ws In Application.Worksheets
    If ws.Name = "January" or ws.name = "february" then
'.... etc
   End if
Next ws
    Hope that helps.
    Phil
    Reply With QuoteRegister To Reply
  3. 11-01-2011, 01:22 PM #3
    Toonies
    Toonies is offline
    Forum Contributor Toonies's Avatar
    Join Date
    07-30-2009
    Location
    Newcastle, UK
    MS-Off Ver
    Excel 2016
    Posts
    511

    Re: Need Help with UserForm populating Multiple Sheets

    I will check it out when I finish work. Many thanks Toonies
    Reply With QuoteRegister To Reply
  4. 11-01-2011, 07:49 PM #4
    Toonies
    Toonies is offline
    Forum Contributor Toonies's Avatar
    Join Date
    07-30-2009
    Location
    Newcastle, UK
    MS-Off Ver
    Excel 2016
    Posts
    511

    Re: Need Help with UserForm populating Multiple Sheets

    I've tried to get it to work but no luck as it does not populate sheets
    Last edited by Toonies; 11-01-2011 at 08:13 PM.
    Reply With QuoteRegister To Reply
  5. 11-02-2011, 04:52 PM #5
    Toonies
    Toonies is offline
    Forum Contributor Toonies's Avatar
    Join Date
    07-30-2009
    Location
    Newcastle, UK
    MS-Off Ver
    Excel 2016
    Posts
    511

    Re: Need Help with UserForm populating Multiple Sheets

    Bump. Anyone
    Reply With QuoteRegister To Reply
  6. 11-03-2011, 03:25 AM #6
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: UserForm populating Multiple Sheets

    The code could be shortened considerably.

    Do you intend using the form with the sheet being used active?

    If you do then use ActiveSheet. If not you need to have a combobox to select the month from, then use, assuming the months are listed in a ComboBox called cboMonths

    Set ws = Sheets(Me.cboMonths.Value)
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example
    Reply With QuoteRegister To Reply
  7. 11-03-2011, 04:14 AM #7
    Toonies
    Toonies is offline
    Forum Contributor Toonies's Avatar
    Join Date
    07-30-2009
    Location
    Newcastle, UK
    MS-Off Ver
    Excel 2016
    Posts
    511

    Re: UserForm populating Multiple Sheets

    yes Roy I would prefer active, but not quite sure on how to do it that way.

    Thanks Tom
    Reply With QuoteRegister To Reply
  8. 11-03-2011, 07:14 PM #8
    Toonies
    Toonies is offline
    Forum Contributor Toonies's Avatar
    Join Date
    07-30-2009
    Location
    Newcastle, UK
    MS-Off Ver
    Excel 2016
    Posts
    511

    Re: UserForm populating Multiple Sheets

    I'm open to any help or ideas

    Many thanks

    Toonies
    Reply With QuoteRegister To Reply
  9. 11-04-2011, 03:55 AM #9
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: UserForm populating Multiple Sheets

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!
    Reply With QuoteRegister To Reply
  10. 11-09-2011, 06:07 PM #10
    Toonies
    Toonies is offline
    Forum Contributor Toonies's Avatar
    Join Date
    07-30-2009
    Location
    Newcastle, UK
    MS-Off Ver
    Excel 2016
    Posts
    511

    Re: UserForm populating Multiple Sheets

    Hi thanks for the advice, I will explain it again

    I am looking to create a single Userform to populate 12 spreadsheets within the same workbook

    January to December depending on the following

    1. Name
    2. Date
    3. Vacation Hours

    I do have a UserForm (shown in the attachment) that will only populate 1 sheet, but I am unable to change the VBA code to work on all sheets from the one UserForm.

    The enclosed example has only 2 sheets to possibly make it easier to workout a solution if there is one.

    Book abc.v1.xls

    The VBA code is a very long as its my first real attempt, it may be possible to reduce the size however at present I do not have the knowledge to do that.

    I have used part of the suggested coding by "incjourn", 

    Private Sub UserForm_Initialize()
    Dim Dys As Integer
Dim N As Integer
Dim Dt As Date
Dys = DateValue("1/1/" & Year(Now)) - DateValue("1/1/" & (Year(Now) - 1))
ReDim Ray(1 To Dys)
Dt = "1/1/" & Year(Now)
    For N = 1 To Dys
        Ray(N) = IIf(N = 1, Dt, DateAdd("d", 1, Dt))
        Dt = Ray(N)
    Next N
    
    NameBox2.List = Application.Transpose(Range("Employees").Value)
    
    For Each ctl In Me.Controls
        If Left(ctl.Name, 3) = "Day" Then
            ctl.List = Application.Transpose(Ray)
        ElseIf Left(ctl.Name, 5) = "Hours" Then
            ctl.List = Range("HolidayHours").Value
        End If
    Next ctl
End Sub
    (however couldn't get the full code to work)

    Cheers

    Toonies
    Reply With QuoteRegister To Reply
  11. 11-10-2011, 03:29 AM #11
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: UserForm populating Multiple Sheets

    I've added a ComboBox to select the month & amended the code to write to that month
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 11-10-2011, 03:57 AM #12
    Toonies
    Toonies is offline
    Forum Contributor Toonies's Avatar
    Join Date
    07-30-2009
    Location
    Newcastle, UK
    MS-Off Ver
    Excel 2016
    Posts
    511

    Re: UserForm populating Multiple Sheets

    Many thanks for your help.

    One slight problem, when I run the amended code I get the following

    "Run-Time error code 13"
    type mismatch

    on this part of the code

    Else: Set ws = Worksheets(Me.cboMonths).Value
    Cheers
    Toonies
    Reply With QuoteRegister To Reply
  13. 11-10-2011, 05:24 AM #13
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: UserForm populating Multiple Sheets

    Try this, there was a typo
    'set selected month
    If Me.cboMonths.ListIndex < 0 Then
        MsgBox "please select the appropriate Month", vbCritical, "Input require"
        Exit Sub
    Else:     Set ws = Worksheets(Me.cboMonths.Text)
    End If
    Reply With QuoteRegister To Reply
  14. 11-10-2011, 05:58 AM #14
    Toonies
    Toonies is offline
    Forum Contributor Toonies's Avatar
    Join Date
    07-30-2009
    Location
    Newcastle, UK
    MS-Off Ver
    Excel 2016
    Posts
    511

    Re: UserForm populating Multiple Sheets

    Hi Roy,

    that got rid of the Run-time Error

    however

    the amended code only populates January ws?

    Many thanks for the help

    Cheers Toonies
    Reply With QuoteRegister To Reply
  15. 11-10-2011, 08:44 AM #15
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: UserForm populating Multiple Sheets

    Read your code throught & see what is happening. The code will populate whatever sheet is selected in the ComboBox, if the sheet exists. If you look through your code for the input you re-set the ws variable to January.

    Your code should be
    Private Sub MarchAdd_Click()
    Dim iRow As Long
    Dim lRw As Long
    Dim lCol As Long
    Dim lCol1 As Long
    Dim lCol2 As Long
    Dim lCol3 As Long
    Dim lCol4 As Long
    Dim lCol5 As Long
    Dim lCol6 As Long
    Dim lCol7 As Long
    Dim lCol8 As Long
    Dim lCol9 As Long
    Dim lCol10 As Long
    Dim lCol11 As Long
    Dim lCol12 As Long
    Dim lCol13 As Long
    Dim lCol14 As Long
    Dim lCol15 As Long
    Dim lCol16 As Long
    Dim lCol17 As Long
    Dim lCol18 As Long
    Dim lCol19 As Long
    Dim lCol20 As Long
    Dim lCol21 As Long
    Dim lCol22 As Long
    Dim lCol23 As Long
    Dim lCol24 As Long
    Dim lCol25 As Long
    Dim lCol26 As Long
    Dim lCol27 As Long
    Dim lCol28 As Long
    Dim lCol29 As Long
    Dim lCol30 As Long
    Dim ws As Worksheet
    Dim ws1 As Worksheet

    Application.ScreenUpdating = False
    
   
        lRw = Me.NameBox2.ListIndex + 6    '< Listindex starts at 0, day1 is on Row3 so add 4
        lCol = Me.Day1.ListIndex + 3
        lCol1 = Me.Day2.ListIndex + 3
        lCol2 = Me.Day3.ListIndex + 3
        lCol3 = Me.Day4.ListIndex + 3
        lCol4 = Me.Day5.ListIndex + 3
        lCol5 = Me.Day6.ListIndex + 3
        lCol6 = Me.Day7.ListIndex + 3
        lCol7 = Me.Day8.ListIndex + 3
        lCol8 = Me.Day9.ListIndex + 3
        lCol9 = Me.Day10.ListIndex + 3
        lCol10 = Me.Day11.ListIndex + 3
        lCol11 = Me.Day12.ListIndex + 3
        lCol12 = Me.Day13.ListIndex + 3
        lCol13 = Me.Day14.ListIndex + 3
        lCol14 = Me.Day15.ListIndex + 3
        lCol15 = Me.Day16.ListIndex + 3
        lCol16 = Me.Day17.ListIndex + 3
        lCol17 = Me.Day18.ListIndex + 3
        lCol18 = Me.Day19.ListIndex + 3
        lCol19 = Me.Day20.ListIndex + 3
        lCol20 = Me.Day21.ListIndex + 3
        lCol21 = Me.Day22.ListIndex + 3
        lCol22 = Me.Day23.ListIndex + 3
        lCol23 = Me.Day24.ListIndex + 3
        lCol24 = Me.Day25.ListIndex + 3
        lCol25 = Me.Day26.ListIndex + 3
        lCol26 = Me.Day27.ListIndex + 3
        lCol27 = Me.Day28.ListIndex + 3
        lCol28 = Me.Day29.ListIndex + 3
        lCol29 = Me.Day30.ListIndex + 3
        lCol30 = Me.Day31.ListIndex + 3
    

   'set selected month
    If Me.cboMonths.ListIndex < 0 Then
        MsgBox "please select the appropriate Month", vbCritical, "Input require"
        Exit Sub
    Else: Set ws = Worksheets(Me.cboMonths).Value
    End If
    
    With ws
        'find first empty row in database
        iRow = .Cells(.Rows.Count).End(xlUp).Offset(1, 0).Row
        .Cells(lRw, lCol).Value = Left(Trim(Me.Hours1.Value), 10)
        .Cells(lRw, lCol1).Value = Left(Trim(Me.Hours2.Value), 10)
        .Cells(lRw, lCol2).Value = Left(Trim(Me.Hours3.Value), 10)
        .Cells(lRw, lCol3).Value = Left(Trim(Me.Hours4.Value), 10)
        .Cells(lRw, lCol4).Value = Left(Trim(Me.Hours5.Value), 10)
        .Cells(lRw, lCol5).Value = Left(Trim(Me.Hours6.Value), 10)
        .Cells(lRw, lCol6).Value = Left(Trim(Me.Hours7.Value), 10)
        .Cells(lRw, lCol7).Value = Left(Trim(Me.Hours8.Value), 10)
        .Cells(lRw, lCol8).Value = Left(Trim(Me.Hours9.Value), 10)
        .Cells(lRw, lCol9).Value = Left(Trim(Me.Hours10.Value), 10)
        .Cells(lRw, lCol10).Value = Left(Trim(Me.Hours11.Value), 10)
        .Cells(lRw, lCol11).Value = Left(Trim(Me.Hours12.Value), 10)
        .Cells(lRw, lCol12).Value = Left(Trim(Me.Hours13.Value), 10)
        .Cells(lRw, lCol13).Value = Left(Trim(Me.Hours14.Value), 10)
        .Cells(lRw, lCol14).Value = Left(Trim(Me.Hours15.Value), 10)
        .Cells(lRw, lCol15).Value = Left(Trim(Me.Hours16.Value), 10)
        .Cells(lRw, lCol16).Value = Left(Trim(Me.Hours17.Value), 10)
        .Cells(lRw, lCol17).Value = Left(Trim(Me.Hours18.Value), 10)
        .Cells(lRw, lCol18).Value = Left(Trim(Me.Hours19.Value), 10)
        .Cells(lRw, lCol19).Value = Left(Trim(Me.Hours20.Value), 10)
        .Cells(lRw, lCol20).Value = Left(Trim(Me.Hours21.Value), 10)
        .Cells(lRw, lCol21).Value = Left(Trim(Me.Hours22.Value), 10)
        .Cells(lRw, lCol22).Value = Left(Trim(Me.Hours23.Value), 10)
        .Cells(lRw, lCol23).Value = Left(Trim(Me.Hours24.Value), 10)
        .Cells(lRw, lCol24).Value = Left(Trim(Me.Hours25.Value), 10)
        .Cells(lRw, lCol25).Value = Left(Trim(Me.Hours26.Value), 10)
        .Cells(lRw, lCol26).Value = Left(Trim(Me.Hours27.Value), 10)
        .Cells(lRw, lCol27).Value = Left(Trim(Me.Hours28.Value), 10)
        .Cells(lRw, lCol28).Value = Left(Trim(Me.Hours29.Value), 10)
        .Cells(lRw, lCol29).Value = Left(Trim(Me.Hours30.Value), 10)
        .Cells(lRw, lCol30).Value = Left(Trim(Me.Hours31.Value), 10)
        'copy the data to the database
        .Cells(iRow).Value = Me.Day1.Value
        .Cells(iRow).Value = Me.Day2.Value
        .Cells(iRow).Value = Me.Day3.Value
        .Cells(iRow).Value = Me.Day4.Value
        .Cells(iRow).Value = Me.Day5.Value
        .Cells(iRow).Value = Me.Day6.Value
        .Cells(iRow).Value = Me.Day7.Value
        .Cells(iRow).Value = Me.Day8.Value
        .Cells(iRow).Value = Me.Day9.Value
        .Cells(iRow).Value = Me.Day10.Value
        .Cells(iRow).Value = Me.Day11.Value
        .Cells(iRow).Value = Me.Day12.Value
        .Cells(iRow).Value = Me.Day13.Value
        .Cells(iRow).Value = Me.Day14.Value
        .Cells(iRow).Value = Me.Day15.Value
        .Cells(iRow).Value = Me.Day16.Value
        .Cells(iRow).Value = Me.Day17.Value
        .Cells(iRow).Value = Me.Day18.Value
        .Cells(iRow).Value = Me.Day19.Value
        .Cells(iRow).Value = Me.Day20.Value
        .Cells(iRow).Value = Me.Day21.Value
        .Cells(iRow).Value = Me.Day22.Value
        .Cells(iRow).Value = Me.Day23.Value
        .Cells(iRow).Value = Me.Day24.Value
        .Cells(iRow).Value = Me.Day25.Value
        .Cells(iRow).Value = Me.Day26.Value
        .Cells(iRow).Value = Me.Day27.Value
        .Cells(iRow).Value = Me.Day28.Value
        .Cells(iRow).Value = Me.Day29.Value
        .Cells(iRow).Value = Me.Day30.Value
        .Cells(iRow).Value = Me.Day31.Value
        .Cells(iRow).Value = Me.Hours1.Value
        .Cells(iRow).Value = Me.Hours2.Value
        .Cells(iRow).Value = Me.Hours3.Value
        .Cells(iRow).Value = Me.Hours4.Value
        .Cells(iRow).Value = Me.Hours5.Value
        .Cells(iRow).Value = Me.Hours6.Value
        .Cells(iRow).Value = Me.Hours7.Value
        .Cells(iRow).Value = Me.Hours8.Value
        .Cells(iRow).Value = Me.Hours9.Value
        .Cells(iRow).Value = Me.Hours10.Value
        .Cells(iRow).Value = Me.Hours11.Value
        .Cells(iRow).Value = Me.Hours12.Value
        .Cells(iRow).Value = Me.Hours13.Value
        .Cells(iRow).Value = Me.Hours14.Value
        .Cells(iRow).Value = Me.Hours15.Value
        .Cells(iRow).Value = Me.Hours16.Value
        .Cells(iRow).Value = Me.Hours17.Value
        .Cells(iRow).Value = Me.Hours18.Value
        .Cells(iRow).Value = Me.Hours19.Value
        .Cells(iRow).Value = Me.Hours20.Value
        .Cells(iRow).Value = Me.Hours21.Value
        .Cells(iRow).Value = Me.Hours22.Value
        .Cells(iRow).Value = Me.Hours23.Value
        .Cells(iRow).Value = Me.Hours24.Value
        .Cells(iRow).Value = Me.Hours25.Value
        .Cells(iRow).Value = Me.Hours26.Value
        .Cells(iRow).Value = Me.Hours27.Value
        .Cells(iRow).Value = Me.Hours28.Value
        .Cells(iRow).Value = Me.Hours29.Value
        .Cells(iRow).Value = Me.Hours30.Value
        .Cells(iRow).Value = Me.Hours31.Value
    End With

    Application.ScreenUpdating = True
    Unload Me

End Sub
    All these lines will fail, there is no Column reference in them
    .Cells(iRow).Value = Me.Hours31.Value
    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)

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