Free Microsoft Excel 2013 Quick Reference

How to populate combo box in vba Results

Im a beginner in VBA and i am designing a form to be used at my work. I have 2 questions.

1) i have added a combo box but cant populate the list.

I was told to do this by a friend

If your Combo is from the Forms toolbar then:

Right click on you Combo and select "Format Control" then click the "Control Tab" now put you range that contains your list in the "Input range" box.

If from the Control Toolbox:

Right click on it and select "Properties" then Scroll down to "ListFillRange" and type your range, e.g A1:A100

You may want to consider using Data>Validation with "List" as your allowed entries.

Hope this helps

but i dont have format control and in the properties list, listfillrange does not appear??? What do i do now?

also

2) I want to set up a txt box that automatically generates a reference number that cant be edited if the form is being filled in.

Can you help?

I had this on another thread that was closed and was told this by paul:

I mean there are two type of controls Forms and active x

active x has the format control
Forms has the properties

you need to select the active x Controls toolbox

or run this
Sub ptester()
With Sheets("1")
    Set pboxes = .Shapes.AddFormControl(xlListBox, 100, 10, 100, 10)
    pboxes.ControlFormat.ListFillRange = "A1:A10"
End With
End Sub
__________________
regards pike

But i dont have format control, i dont know how to add it and i dont know where to insert this code so it works???????

Please help!

Hi,

I'm using a combo box in a userform and using "Add Data" in the VBA code to
populate the drop down options...so far so good. However I'm having
difficulty selecting the correct properites of the combo box, to prevent the
(human) user from selecting & editing the options in the drop down, when the
userform appears. Everything I try prevents the dropdown from actually,
well..dropping down.

Also, is it possible to disable or make invisible a button on a worksheet
when a condition arises (say a cell changes to a preset value). I can make
the button "grey out" or disappear on a UserForm, but I can't see how to do
it if it's on a worksheet. OK, I could set a condition in the code for it not
operate if pushed, but "greying out" would be neater.

Thanks...Chris

Hello,

I'm fairly new to VBA Programming and I'm working on a project at work and I'm a bit lost on what to do next. I'll try to explain this as clearly as possible: I have a combo box and text box as below:

Combo Box Text Box

The user will select a position from the drop-down list in the combo box. They will then enter in data in it's respective text box. Once they complete this, they will hit the OK button, and it will then automatically populate the respective cells on the worksheet.

I've managed to figure out how to transfer the data from a simple text box to a cell on a worksheet but I'm confused on how to transfer that data when there is a combo box in the equation (since the location of the transfer depends on the position selected from the combo box).

Any help will be greatly appreciated! I hope this makes sense!!

Hello guys,

I need to populate certain lists in the combo boxes while following these rules.

There are 3 classes in three different worksheets.Every class has 6 sections.Each section has certain number of students which is dynamic.More number of students might be added later, though it'll not be more than 50.I 'll have 3 comboboxes in the userform. I need to select the Class and Section based on which the 3rd combobox should automatically populate the list of students available. Please check the attached spreadsheet.Populating Combo Boxes based on selection.xlsm

I am planning to have a LookupLists spreadsheet where I can populate the students based on formulas and then use VBA to add them to the combobox, but am not sure how easy it will be.

What is the easiest way to achieve this task?

Thanks in advance,

Regards,
Ravi.

I want to make a form that very interactive, will drive macros to change items the user sees, and to capture the data entered by the user on a hidden page.

I am pretty sure I need a combobox, not a dropdown since its more robust. I just cant figure out how to populate it with choices, without vba manually adding items to it. I can find the Rowsource property.

can someone show me, 1, how to add a range to a combo box without vba code adding it?

And 2, can someone show me how to change Sheet1, Cell A1's value to display the combo box selection?

I do want the default to be blank. And the options could be, Option1, 2 and 3.

Thanks in advance.
MK

I have a VBA UserForm with 4 Combo boxes . These are dynamically populated by reading unique values from its corresponding column on a sheet and placing them in another sheet which the combo box reads.

The problem is that the user wants the following they select a value from ComboBox1. The remaining 3 combo boxes in effect requery to display choices only based on associations with the selection of ComboBox1.

The user then may select something from one of the other 3 Combo Boxes and would like the process to repeat itself and the remaining 2 in effect requery to choices associated with the first 2 selections.

I am trying to simply mimic what excel can do in the Auto filters but provide an interface that looks pretty.

I have done this many times in Access but was wondering how to approach this on a VBAS User Form in Excel. Thanks

I have a program that is used as a client login (electronic sign-in sheet). The opening screen is a UserForm where the client enters their ID #. On this screen I also have a Label control that displaces important information to the client. The information being displayed in the Label changes regularly. I would like for the employee that maintains the login program to be able to update the Label information without going into the VBA code. I have a tab labeled "Variables" which currently contains information that I use to populate several Combo Box controls. Is there a way to populate the text on the Label using one of the cells on the "Variables" tab?

I thought this would be as simple as it is to populate the Combo Boxes, but so far I have not come up with the right code to do it. I have done several searches and have not found anything that has given me the answer I am needing. Any help will be greatly appreciated.

Hi

I have a user form which contains a number of combo boxes. For the sake of description the left hand boxes describe how things stand today the right hand boxes ask user to supply entries for the future state. The number of rows in each combo box is 4 and both left and right sided combo boxes share the same list.

As I know that certain pairings in the left and right list row entries are not possible(and therefore user input errors) I need to test the users selection before submitting selections to the work sheet.

In my head I am thinking if I can add an extra column to my list (a numerical score) and increase the column count to two column in my combo boxes I could test logically the numerical values in the left and right side. So for instance if the left combo selection was "Disabled before accident" with a numeric value of 1 (in column two of the list row data) and the user entry in the right combo was " Not disabled after accident" with a list row second column value of 2 I could simply test for combo box left column 2 is equal to combo box right column 2 to validate each side.

I still need the user entry in the first column "Disabled before accident" for example to populate my cell in the worksheet so how would i use the numeric value in the second column of the combo boxes just for the purposes of the Userforms vba to test the user selection?

Many thanks

Hi, I am attempting to create 3 combo boxes that will pull source data over from another worksheet in my workbook.

I don't need them to be dependent upon each other, I am fairly new to Excel programming (use Access and some VBA mostly) and can't seem to find the right keyword(s) to search on for what I need.

I have attached a pic of what I am looking for.

cboMonth
cboDept
cboOrderType

I can get the combo boxes populated with my lists of data, just need to find out how to add VBA to have it change the corresponding data below the combo boxes by pulling the data from the source worksheet. I have seen the VLOOKUP method but am hoping there is an easier VBA method to do this. I want it to change no matter what combination of combo box is chosen i.e. if they change the Month and Dept only, don't change the Order Type.

What should I be searching on? I've seen posts on Data Validation but unsure if this is what I need.

Would appreiate any help you could give me.

Thanks!

Toni

Hi,

I am new to VBA and I am trying to manipulate a form that someone else created within Excel. The form has 3 ActiveX ComboBoxes (UnitComboBox1, TypeComboBox1, TaskComboBox1) among other things (checking rows for data, submitting form to email list, approve, disapprove buttons that goto different email lists).

There are a few things that I would like to do with my ActiveX comboboxes
1.) First when a user selects from the UnitComboBox1, based on the Unit they select, it populates the TypeComboBox1, and then again when a user selects the TypeComboBox1, based on the Type they select, it populates the TaskComboBox1.
2.) Depending on the combinations that the user selects of the 3 combo boxes, it needs to send the form via email to a select group of people (for which I have created an array).

I know this is possible, but have not been able to find an example of how to do 3 combo boxes, using SelectCase and being that I am new to all of this, I don't even know where to begin to VBA code this using Active X.

There are also different email lists (depending on the selections in the comboboxes) that this form will get sent to.

Hope I am making sense....ANY help/guidance would be GREATLY appreciated.

I have included the Excel sheet VB below. Thank you!

[email]VB@N3wb|3


	VB:
	
 Calendar2_Click() 
    [G4] = Calendar2.Value + Time 
    Me.Calendar2.Visible = False 
    Me.DateCommandButton4.Visible = True 
End Sub 
 
Private Sub cmdSend_Click() 
    Dim Recip As Variant 
    Dim c As Range 
    Dim r As Integer 
    Dim c1 As Range 
    Dim r1 As Integer 
    Dim c2 As Range 
    Dim r2 As Integer 
    Dim RowComplete As Boolean 
     
    r2 = 0 
     
    If [E4]  "" Then 
         
        If [G4]  "" Then 
             
            r1 = 12 
             
            For Each c2 In Range("a14:g19") 
                If c2 = "" Then 
                    r2 = r2 + 1 
                End If 
            Next c2 
             
            If r2 = 42 Then 
                MsgBox "You must have at least 1 complete row filled in before sending this form." 
                Exit Sub 
            Else 
                 
                For Each c1 In Range("e14:e19") 
                    r = 0 
                    For Each c In Range("a" & r1 & ":" & "g" & r1) 
                        If c = "" Then 
                            r = r + 1 
                        End If 
                    Next c 
                    If r  6 Then 
                    End If 
                    r1 = r1 + 1 
                    RowComplete = True 
                Next c1 
                 
                If RowComplete = True Then 
                    
'********************************************************************************************************************************************************************
                     '*****************************************TEST
Address***************************************************************************************************************
                     '                            Recip = Array("anyemail@anyaddy.com")
                     '*****************************************End TEST
Address***********************************************************************************************************
                    
'********************************************************************************************************************************************************************
                     
                    Recip = Array("emails@email.com", "email@email.com") 
                     
                    ActiveWorkbook.SendMail Recipients:=Recip 
                     
                     
                End If 
            End If 
             
        Else 
            MsgBox "You must enter in the date for this request.  Click the Date button for a pop up calendar." 
        End If 
    Else 
        MsgBox " You must choose a Requesting Unit/Activity from the dropdown list provided" 
    End If 
     
End Sub 
 
Private Sub DateCommandButton4_Click() 
    Me.Calendar2.Visible = True 
    Me.Calendar2.Value = Date 
End Sub 
 
Private Sub cmdApproved_Click() 
     
    Dim Password As String, ComparePW As String 
     
    Password = "rev22jan07" 
     
    ComparePW = InputBox("Enter the Password") 
     
    If Len(ComparePW) = 0 Then 
        Exit Sub 
    ElseIf ComparePW = Password Then 
        Me.lblApproved.Visible = True 
        DeleteButtons 
    End If 
     
End Sub 
Private Sub cmdDisapproved_Click() 
     
    Dim Password As String, ComparePW As String 
     
    Password = "rev22jan07" 
     
    ComparePW = InputBox("Enter the Password") 
     
    If Len(ComparePW) = 0 Then 
        Exit Sub 
    ElseIf ComparePW = Password Then 
        Me.lblDisapproved.Visible = True 
        DeleteButtons 
    End If 
     
End Sub 
Private Sub lblDisapproved_Click() 
     
End Sub 
 
Private Sub UnitComboBox1_Change() 
     
End Sub 
 
Private Sub TypeComboBox1_Change() 
     
End Sub 
 
Private Sub TaskComboBox1_Change() 
     
End Sub 
 
Private Sub worksheet_selectionChange(ByVal target As Range) 
     
    If Me.Calendar2.Visible = True Then 
        Me.Calendar2.Visible = False 
        Me.DateCommandButton4.Visible = False 
    End If 
     
End Sub 
 
Sub Mail_Workbook() 
     
    Dim Recip As Variant 
    Recip = Array("emails@email.com", "email@email.com") 
     
    ActiveWorkbook.SendMail Recipients:=Recip 
End Sub 
 
Function DeleteButtons() 
     
    Dim Recip As Variant 
    Dim c As Range 
    Dim r As Integer 
    Dim c1 As Range 
    Dim r1 As Integer 
    Dim c2 As Range 
    Dim r2 As Integer 
    Dim RowComplete As Boolean 
    Dim vApproval As String 
    r2 = 0 
     
    If [E4]  "" Then 
        If [G4]  "" Then 
            r1 = 12 
            For Each c2 In Range("a14:G19") 
                If c2 = "" Then 
                    r2 = r2 + 1 
                End If 
            Next c2 
            If r2 = 42 Then 
                MsgBox "You must have at least 1 complete row filled in before sending this form." 
                If Sheet1.lblApproved.Visible = True Then 
                    Sheet1.lblApproved.Visible = False 
                ElseIf Sheet1.lblDisapproved.Visible = True Then 
                    Sheet1.lblDisapproved.Visible = False 
                End If 
                Exit Function 
                 
            Else 
                For Each c1 In Range("F14:F19") 
                     
                    r = 0 
                    For Each c In Range("a" & r1 & ":" & "G" & r1) 
                        If c = "" Then 
                            r = r + 1 
                        End If 
                    Next c 
                     
                    If r  7 Then 
                        If r  0 Then 
                            MsgBox "This form must be filled out in its entirety before being sent.  Please complete the
missing data in row " & r1 & " or delete the data already entered" 
                            If Sheet1.lblApproved.Visible = True Then 
                                Sheet1.lblApproved.Visible = False 
                            ElseIf Sheet1.lblDisapproved.Visible = True Then 
                                Sheet1.lblDisapproved.Visible = False 
                            End If 
                            Exit Function 
                        End If 
                    End If 
                    r1 = r1 + 1 
                    RowComplete = True 
                Next c1 
                If RowComplete = True Then 
                    If Sheet1.lblApproved.Visible = True Then 
                        vApproval = "APPROVED" 
                    ElseIf Sheet1.lblDisapproved.Visible = True Then 
                        vApproval = "DISAPPROVED" 
                    End If 
                     
                     
                    Sheet1.cmdDisapproved.Visible = False 
                    Sheet1.cmdApproved.Visible = False 
                     
                    ActiveWorkbook.SaveAs vApproval & " - Request " & Sheet1.Range("E4") & "-" & Format(Sheet1.Range("G4"),
"dd mmm yyyy h:mm AM/PM") 
                     
                    
'********************************************************************************************************************************************************************
                     '*****************************************TEST
Address***************************************************************************************************************
                     '                        Recip = Array("anyemail@anyemail.com")
                     '*****************************************End TEST
Address***********************************************************************************************************
                    
'********************************************************************************************************************************************************************
                     
                    Recip = Array("Recip = Array("emails@email.com", "email@email.com")") 
) 
                     
                     
                     
                     
                     
                     
                    ActiveWorkbook.SendMail Recipients:=Recip 
                     
                     
                End If 
            End If 
        Else 
            MsgBox "You must enter in the date for this request.  Click the Date button for a pop up calendar" 
            If Sheet1.lblApproved.Visible = True Then 
                Sheet1.lblApproved.Visible = False 
            ElseIf Sheet1.lblDisapproved.Visible = True Then 
                Sheet1.lblDisapproved.Visible = False 
            End If 
        End If 
    Else 
        MsgBox "You must choose a Requesting Unit/Activity from the dropdown list provided" 
        If Sheet1.lblApproved.Visible = True Then 
            Sheet1.lblApproved.Visible = False 
        ElseIf Sheet1.lblDisapproved.Visible = True Then 
            Sheet1.lblDisapproved.Visible = False 
        End If 
    End If 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello there!

I've just stumbled accross this forum in search of an answer to a vba problem that I have so I thought I'd post and see if you could help me. excuse me if I'm not good with the terminology...

I'm trying to create a data entry form that uses comboboxes. I can populate the comboboxes with data from an excell sheet. There are 3 columns of data that are called to each combobox, and the list of data is the same in each combobox. When the chosen line is selected for each combobox I then want to pass all that data back to the excel sheet in the same column layout. I suspect this will need to be done with an array.

Basically, I want to have five tasks with timing details that are done from Monday to Friday - these are listed in excel. I want each of these tasks to appear in each combo box (two columns in the combobox) so that the user can decide which task will be done on which day. Then the tasks are passed back to excel as a timetable. If it were 1 column I could do it but the second column is causing me grief. I suspect this will need to be done with an array.

Clear as mud?!?

Hope someone can help me!

Cheers,

FIN

Hi guys,
I have worked out the syntax for populating a combo box with a list from the same worksheet, how do I have to change the syntax if the list is on a different sheet in the same workbook.
I really appreciate any help I can get with this one.
Thanks
Trac

Hi all,

I have a form which i bound to a query, in which it gets all the text box's populated from, apart from 2.

there two combo box's the user needs to populate.

so on my table, in record 10 all my fields are populated apart from PRODUCT.

when i open up my form,, and the user scolls to record 10, and chooses a selection from COMBOBOX110, how do i get VBA to update, that field, on that perticular record its looking at, but only populate that field if it changes from last time they scrolled through.

I created a series of combo boxes to accept values from the user. These values are intended to be used to build the name of a file they want to see. I tried to set whatever gets selected in each of the combo boxes equal to a distinct variable and inserted each variable into the file path at the appropriate point. I then created a variable for the file path and inserted the file path variable into code that opens that file, copies a particular worksheet, and pastes it into a new sheet in the “master” (the one with the form) workbook and labels it according to what it was named before.

The problem I’m having is that Excel says it doesn’t recognize the file path I’ve given it. Judging from the file path it quotes in the message, it’s not seeing my variables (only the part of the file path I entered in quotes) and then, later when I “debug” it says the variables are not defined.

I’m wondering if there is more to defining a variable than what I’ve done. On the form page of code, I declared the following variables:
The UserForm Code

	VB:
	
 
Public strPerformBySubtask As String 
Public strPerformBySubelement As String 
Public strPeriod As String 
Public strMonth As String 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I populated each combo box ok and remembered to call the sub procedures below, show the form, hide the form, et al.

I think the problem might be in the sub procedures below that I thought would define the variables by setting the respective combo box output equal to a distinct variable I could use in the file path:


	VB:
	
 GetWhichDates() 
     'set the month variable equal to whatever is picked in the combo box
    strMonth = cboMonth 
End Sub 
 
Sub GetWhichSubtask() 
     'set the subtask variable equal to whatever is picked in the combo box
    strPerformBySubtask = cboSubtask 
End Sub 
 
Sub GetWhichSubelement() 
     'set the subelement variable equal to whatever is picked in the combo box.
    strPerformBySubelement = cboSubelement 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Question #1) Is this not how to get Excel to recognize the variable as the output of the combo box?

Question #2) Is this how to get Excel to see “1” when the user selects “Jan” and so forth:

	VB:
	
 ConvertMonthSelectedToNumber(strMonth) 
    Select Case strMonth 
    Case "Jan" 
        strMonth = 1 
    Case "Feb" 
        strMonth = 2 
… 
    Case "Dec" 
        strMonth = 12 
    End Select 
End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


To finish my illustration… In a separate module page I created distinct variables to equal the worksheet, the worksheet name, and the file path and inserted the variables from the form as follows:


	VB:
	
strSheet = "Task Order" & strPerformBySubtask & "-" & strPerformBySubelement 
 
strSheetName = strPerformBySubtask & "-" & strPerformBySubelement 
 
strFile = "D:Documents and SettingsmccaralDesktopMP2 MONTHLY FINANCIALS" _ 
& strPerformBySubtask & "-" & strPerformBySubelement & "2007" & strMonth & ".xls" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Then, I inserted the variables into a bit of code that in a test case (ie manual entry), would open up the file the user selected, copy a user selected worksheet, name it, and add it as a new worksheet to my master workbook:


	VB:
	
Workbooks.Open Filename:=strFile 
Sheets(strSheet).Select 
Range("A1:P15").Select 
Selection.Copy 
Windows("SUBCONTRACTS_MASTER_WORKBOOK_ii.xls").Activate 
Sheets.Add 
ActiveSheet.Name = strSheetName 
ActiveSheet.Paste 
Columns("C:O").EntireColumn.AutoFit 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Only trouble is, it doesn’t work when I use the variables.

Any insight would be greatly appreciated. I’m new to VBA and pretty confused at this point. I took a class not too long ago and so the information is just fresh enough to make me think I can almost understand what’s going on. Thanks for reading along at any rate.

Hello,

I am trying to figure out how to delete a row on a worksheet via a VBA
User Form...

I currently have a User Form with a Combo Box that is populated by
cells in a named range ("PickCategory...") on a worksheet... I am
populating thsi box using this code:


	VB:
	
 UserForm_Activate() 
    ComboBox1.List = Worksheets("data").Range("PickCategory...").Value 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

First off, I want to include a lable that will display the contents of
the cell to the right of the currently selected entry of the combobox
on a label (or textbox).
So, whatever entry you select in the combo box, the corredponding value
(to the right of it on the worksheet) will appear in the text/label
box...
Is there something similar to the VLOOKUP that will work in VBA?

Also, what I want to be able to do is to allow the user to select one
of the entries from the combo box (which is already working) and then
be able to delete the row of that entry on the worksheet.
So, the user selects an entry from the combo box and then clicks a
"Delete" command button to delete the row of that entry on the
worksheet...

Is this possible? Do you need more information than this?

Thank you in advance!!

While I know this could be accomplished simply using a filter, I'd like to try to add a more user-friendly lookup feature to a sheet. My data consists of a list of names in column D with a list of sports and honors in columns O through CZ. For example:

D11: Joe Smith
O10:T10 (merged): Basketball
O11: Years Played
P11: Years Managed
Q11: Years Captained
R11: Years All Conference
S11: Years All County
T11: Years All Metro
U10:Z10 (merged): Badminton
U11: Years Played
etc.

What I'd like to do is utilize a combo box in H4 to pull up a name (I'd love to use autocomplete within this box but I know that's entirely another question) and have a formula yank the appropriate data and list only the sports played, how many years, etc. Something like this:

H4: Smith, Joe
I4: 4 Years Basketball
J4: 2 Years All Conference
I5: 2 Years Baseball
J5: 2 Years Manager
I6: 3 Years Lacrosse
J6: 2 Years All Conference
K6: 1 Year All Metro

Is there an easy way to do this? This sheet is already calculation-full, so much so that I've switched to manual calculations. Would it be easier to utilize a user-form? I'd need a lot of help creating that as well as I'm a complete rookie when it comes to VBA. Formulas I can work with; VBA... not so much.

Hope that all makes sense. Please excuse any stupid questions. Any help is very much appreciated!

I am fairly new to VBA, so please excuse me if this is very simple. I have a combobox that is populated by a range in a sheet. I also have another combobox that is populated as a result of the first combobox. This is the code that fills the second combo box.

Code:
Private Sub cboIng1_Change()

    With cboUnit1
        .AddItem WorksheetFunction.VLookup(cboIng1.Value, Range("Ing"), 6)
        .AddItem WorksheetFunction.VLookup(cboIng1.Value, Range("Ing"), 8)
        .AddItem WorksheetFunction.VLookup(cboIng1.Value, Range("Ing"), 10)
    End With

End Sub
When I try to clear the form by running the initialize call, i get an error saying that it is unable to lookup the worksheet class. I believe because it is trying to do a lookup after the first combobox is cleared. So what would I put in to clear the second combobox? '

Also, I need the second combobox to refresh after the first one is changed. I think the same code for the clear would go in, but where? Thanks

This is my initialize code right now:
Code:
Private Sub UserForm_Initialize()
   
    Dim cProductName As Range
    Dim ws As Worksheet
    Set ws = Worksheets("IngredientList")
    
    With cboCategory
        .AddItem "Prep"
        .AddItem "Entree"
        .AddItem "Appetizer"
        .AddItem "Side"
        .AddItem "Pasta"
        .AddItem "Sandwich"
        .AddItem "Pizza"
        .AddItem "Salad"
        .AddItem "Dessert"
    End With
    
    For Each cProductName In ws.Range("IngName")
     With Me.cboIng1
     .AddItem cProductName.Value
     .List(.ListCount - 1, 1) = cProductName.Offset(0, 1).Value
     End With
    Next cProductName
    
    For Each cProductName In ws.Range("IngName")
     With Me.cboIng2
     .AddItem cProductName.Value
     .List(.ListCount - 1, 1) = cProductName.Offset(0, 1).Value
     End With
    Next cProductName
    
    For Each cProductName In ws.Range("IngName")
     With Me.cboIng3
     .AddItem cProductName.Value
     .List(.ListCount - 1, 1) = cProductName.Offset(0, 1).Value
     End With
    Next cProductName
    
    txtName.Value = ""
    txtName.SetFocus
    
    cboCategory.Value = ""
    
    cboIng1.Value = ""
    cboIng2.Value = ""
    cboIng3.Value = ""
    
    txtQty1.Value = ""
    txtQty2.Value = ""
    txtQty3.Value = ""
    
    txtCost1.Value = ""    

End Sub


Hi, I am new to VBA and is muddling through it. I am trying to create a form so that a couple of us can enter student lunch choices for our school. This is what I have:

I have a workbook with 2 spreadsheets: Orders and Students. I have created a form that will be activated by a command button in the Orders spreadsheet. This Order form looks like a single month. Each day on this form is a frame. In the frame are 1 to 3 checkboxes and corresponding textbox(s). The checkboxes lists the lunch choices available for the day. The textbox(s) is(are) for the user to enter the number of lunches for the checked lunch (i.e. student wants 3 slices of pizza). There is a combo box which contains the students in the school. The combo box is populated by a named range in rowsource. The named range points to a column on the Students spreadsheet. I also have some fields that will show the total cost for the selected lunches (except for pizza, all lunches cost the same). There are also a save, clear, and exit button. The save button will write a row for each lunch choice to the Orders spreadsheet. The clear button will reinitialize the form. The exit button will return to the Orders spreadsheet and close the form.

Onchange in the combobox, I want to use the Find method to search for the student in the Student spreadsheet to pull in the class and homeroom data.

Now, my first set of (many) questions, can I use the Find method to search the Student spreadsheet? How do I pull the Class (column C) and Homeroom (column D) and populate 2 label fields in the form.

Here is what I have (I have not even tested this code yet):

Dim wks As Worksheet
Dim rFoundResult As Range
Dim sLookingFor As String
Set sLookingFor = cboStudent.Value
Set wks = Worksheets("Students")
wks.Activate
wks.Range("A1").Activate -- where student name is located

Set rFoundResult = wks.Cells.Find(What:=sLookingFor, _ after:=wks.Range("A1"), LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)

If rFoundResult Is Nothing Then
labGrade.Caption = ""
labHomeroom.Caption = "Student NOT FOUND"
Else
labGrade.Caption = -- put grade from Student here
labHomeroom.Caption = -- put homeroom from Student here
End If

Hello

I am new to VBA but I am fairly decent with excel formulas.

What I'm trying to do is automate an order form when I receive an order.

I created a multi-page userform with combo boxes...and I have the comboboxes already referring to a range on another sheet.

What I need to do is make it so that when I select an item from combobox1, some fields in my order form will automatically populate what is next to it.

For example, in the picture I attatched, I selected "ARK-S0104-P" from one of the top comboboxes. That refers to sheet "Cost" and cell A5. Now in my orderform, when a5 is selected from the combobox list, I want different cells automatically refer to that Value, and also the values at B5, C5, D5, E5, F5, G5, H5, etc

The picture of the userform:
[img=http://img216.imageshack.us/img216/3180/userformpicob6.th.jpg]

Hopefully that makes sense. Is there an easy way to do what I'm trying to do?

Thank you!

I am in need of help on an Excel and Access request.

I have a spreadsheet that needs several lists (right click on the cell, "Create List"). The data that needs to populate the lists is stored in an Access table.

I realize that the entire project should be done in an Access Form; however that is not an option unfortunately.

The problem is, the user wants the ability to add a new row in the spreadsheet and have the list be inserted in the new row...OK, that is a bit confusing.

Basically, the tool is a data entry spreadsheet where some values are entered, others chosen from a list. Currently, the user inserts a row when they need to add another line of data.

When the tool was developed (not by me), the list values were kept in a worksheet tab in the spreadsheet. When the Excel file became too large, I was asked to make the spreadsheet the front-end, with all the data in backend.

I am struggling to figure how to keep the dynamic addition of rows possible. I was thinking of using a combo box with VBA code, but it would limit the user to the number I create. Or so I understand.

I would really appreciate some help on this. Thanks!