1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel 2010 User Form Macro

Discussion in 'Software Development' started by lexoned, Apr 23, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. lexoned

    lexoned Thread Starter

    Joined:
    Apr 23, 2012
    Messages:
    2
    Good Day
    I am fairly new to code and writing Macros.
    I am workig in Excel 2010 and creatinfg an estimating tool for bidding condtruction project
    My goal is to create a user control form (Done)
    Parts DB (Done)
    automate the creation of the proposals and project file using the user form
    My issue now is creating the macros to input the dats

    my list, combo boxes see the cell ranges by the range put in the control source that works fine
    I currently can not figure out the Macro to input new date if the current infromation is not listed
    example: col B=project Name. row 1 is title. B2 = bakery, B3 = Bank. I ned my next entry to input new information in B4 then auto populate a new qu0te number in col A

    The user form has function over the entire workbook and is located in the VBA this workbook load upon opening

    I apoligizes if my terms or explinations are not clear as I said I am really very new to this and need all the assistance I cane get
     

    Attached Files:

  2. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    770
    Hi,

    have you resolved this? if not then please can you provide a little morei nformation about what you need help with and i will try help.
     
  3. lexoned

    lexoned Thread Starter

    Joined:
    Apr 23, 2012
    Messages:
    2
    Thanks for your reply
    any assistance to get back on track with this project would be great

    To date

    I have combined all my work books to one becuase I can not figure out how to link my user form to the external worksheets

    My next step and issue is adding new data to several work sheets that store use the information.
    also having a problem figuring out how to use lookup from the user form in the item tabs


    any other information you may need just ask I ll try to get it for you

    Did you see my attachment
     
  4. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    770
    So basically on the userform, Project management tab, you want the drop down boxes to read the values from the workbook, and when you press create quote, you want the values to be saved to the next free row in the same workbook?
     
  5. Upandcoming

    Upandcoming

    Joined:
    Jun 4, 2012
    Messages:
    3
    Sub Test()
    '
    ' Test Macro
    '
    '
    ActiveCell.FormulaR1C1 = "=IF(AND(LEFT(RC[-6],3)=""Que"",RC[-1]<500),1,0)"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A62")
    ActiveCell.Range("A1:A62").Select
    ActiveWindow.SmallScroll Down:=6
    End Sub

    This is how my code looks. I'm trying to continue the formula to auto fill until the last row if I decided to add more rows of data. Currently it'll stop at A62 even if I had 15 new rows or data. Help!!!
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,572
    First Name:
    Hans
    Hi, excuse my butting but your userform does not do any initializing and you have hard-coded a path in the macro code.
    This is strictly speaking 'not done', ik you decide that somebody else is going to run the file from his / het My Documents Al will not be found.

    Code:
            Application.ScreenUpdating = False
            ' open the source workbook as ReadOnly
            Set quotetracking = Workbooks.Open("[B][COLOR="Red"]C:\User\Al\My Documents\New Folder\[/COLOR][/B]quotetracking.xls", _
                False, True)
            ListItems = quotetracking.quotelist(1).Range("B2:B21").Value
            ' get the values you want
            SourceWB.Close False ' close the source workbook save changes
            Set SourceWB = Nothing
            Application.ScreenUpdating = True
            ListItems = Application.WorksheetFunction.Transpose(ListItems)
            ' convert values to a vertical array
    
    for starters:
    Replace the red text with Activeworkbook.path
    the code will then look like follows

    Code:
            Application.ScreenUpdating = False
            ' open the source workbook as ReadOnly
            Set quotetracking = Workbooks.Open([B][COLOR="Red"] ActiveWorkbook.Path & [/COLOR][/B]"\quotetracking.xls", _
                False, True)
            ListItems = quotetracking.quotelist(1).Range("B2:B21").Value
            ' get the values you want
            SourceWB.Close False ' close the source workbook save changes
            Set SourceWB = Nothing
            Application.ScreenUpdating = True
            ListItems = Application.WorksheetFunction.Transpose(ListItems)
            ' convert values to a vertical array
    
    The part with the file location occurs more times so you aill have to edit all ocurrence or use a public variable which is initialized when to form opens,

    Example include this part of the code to the code for the userform.

    I commented the initialize part because it contains errors.


    Code:
    Public fPath As String
    Private Sub UserForm_Activate()
        fPath = ActiveWorkbook.Path
        Me.Caption = Me.Caption & "Filepath: " & fPath
        ' userdataentery_Initialize
    End Sub
    
    
    The caption part is just to show the path that is being used

    There's a long way to go... :)
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1050469

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice