# Excel 2010 User Form Macro



## lexoned (Apr 23, 2012)

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


----------



## DataBase (Jan 15, 2004)

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.


----------



## lexoned (Apr 23, 2012)

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


----------



## DataBase (Jan 15, 2004)

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?


----------



## Upandcoming (Jun 5, 2012)

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!!!


----------



## Keebellah (Mar 27, 2008)

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.


```
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


```
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.


```
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...


----------

