Free Microsoft Excel 2013 Quick Reference

Inputbox....combobox?

At present I have an inputbox getting a string from the user.
Is it possible to have a similar simple box appear but have a combobox instead, and as such restricting the user to limited inputs.

I realise that I can get around this but creating a form with a combobox or in fact putting a combo box on a worksheet, but just wondered if an inputbox style combobox was possible.


Hello everyone.
What I've got is an UserForm with an InputBox. Whatever user gonna type there - it will be used as a name for new worksheet.
It's fine and it is working. But what I would like to do is to add a ComboBox with selection of 3 words (for example: car, truck, bike). I need that in my future code where I will be looking for a worksheet based on its name, so those 3 words from combobox would be kind of a tags for each worksheets. For example:
Dim ws as Worksheet
If ws.Name Like *car* Then
So in that case it wouldn't matter what name the user would give to the new worksheet, cos he will have to chose a type from ComboBox.
So the final name would something like that:
strName = InputBox + ComboBox
ActiveSheet.Name = strName
Bellow is a working code for a user form without a comboBox.

Sub GetSheetName()

Dim strName As String
Dim iReply As Integer


    strName = InputBox(Prompt:="Please, Enter new Sheet name for collected Data.", _
          Title:="WorkSheet new name", Default:="<default>")

          

        If strName = "<default>" Or _
           strName = vbNullString Then

         iReply = MsgBox(Prompt:="Wrong Name, Please Press  OK to Try Again or Press Cancel to EXIT", _
            Buttons:=vbOK, Title:="ERROR")
            
            If iReply = vbOK Then

        Run "GetSheetName"
 
    Else '(VbCancel)

        Exit Sub

    End If




        Else

          Worksheets("Sheet3").Activate
          
          ActiveSheet.Name = strName
         Workbooks("All_2_withSorting.xlsm").Worksheets.add().Name = "Sheet3"
         Worksheets("Sheet1").Select
        End If

  

End Sub


I am new to VBA and thought I could accomplish this feat through various forum threads. So far so good but I'm stuck now. As the title suggest, my objective is to build these security features into a very large workbook/database:When workbook is opened all worksheets are automatically protected and hidden (xlVeryHidden);then a UserForm with a Combobox, Inputbox and CommandButtons (Ok+Cancel) is displayed;Users then select from the Combobox list (8 items) and enter a password (text string) into the Inputbox and click Ok (each listed item has its own password recorded in a hidden worksheet range);If combination of listed item and password is validated a the relevant worksheets are made visible (non relevant worksheets remain "veryHidden") and;the UserForm is either hidden/minimised(ribbon controls/right click menu etc), however should be able to call the UserForm from the current view with ease.If combination is invalid, an error message is displayed and requested to try again. Cancel button should close workbook, and top-right 'X' (exit button) should close workbook as well.I have managed, through various threads, to code steps 1 and 2, and others for the Cancel and 'X' buttons. I have also found a few threads to help with coding the inputbox for passwords but have not progressed until the combobox issue is resolved.

Where I have been struggling the most is how to assign the names in the combobox list to their respective worksheets so that when selected only those worksheets are visible.
see the relevant code below.
Can someone please, please, help.

Code for ThisWorkbook object:

	VB:
	
 Workbook_Open() 
    Dim sht As Worksheet 
     '
    Application.EnableEvents = True 
    Application.ScreenUpdating = False 
    ActiveWorkbook.Unprotect wbPassword 
    For Each sht In ActiveWorkbook.Worksheets 
        With sht 
            .Protect Password:=wsPassword, UserInterfaceOnly:=True 
            .EnableOutlining = True 
            .Visible = xlSheetVeryHidden 
        End With 
    Next sht 
    ActiveWorkbook.Protect wbPassword 
     '
    frmCtrAccess.Show 
     '
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
'
Code for Userform object:

	VB:
	
Private Sub UserForm_Initialize() 
    With Me.ComboBox1 
        .Clear 
        .AddItem "Fish" 
        .AddItem "Amphibian" 
        .AddItem "Bird" 
        .AddItem "Insect" 
        .AddItem "Mammal" 
        .AddItem "Reptile" 
        .AddItem "Animals" 
        .AddItem "Master" 
    End With 
End Sub 
 '
 'calls the ChangeCombo procedure when item is selected from combobox list
Private Sub ComboBox1_Change() 
    ChangeCombo 
End Sub 
 '
 'closes the workbook when the top-right 'X' on UserForm is clicked
Private Sub UserForm_Terminate() 
    ThisWorkbook.Close (savechanges = False) 
End Sub 
 '
 'closes the workbook when the commandbutton 'Cancel' is clicked
Private Sub CommandButtonCancel_Click() 
    ThisWorkbook.Close (savechanges = False) 
End Sub 

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

	VB:
	
 ChangeCombo() 
    Dim wsFish(3) As Variant 
    Dim wsAmphibian As Variant 
    Dim wsBird As Variant 
    Dim wsInsect As Variant 
    Dim wsMammal As Variant 
    Dim wsReptile As Variant 
    Dim wsAnimals As Variant 
    Dim wsMaster As Variant 
    Dim strComboValue As String 
     '
    wsFish(3) = Sheets(Array("Freshwater Fish", "Saltwater Fish", "Sharks_Rays", "Sea Creatures")) 
     '
    wsAmphibian(1) = Sheets(Array("Frogs_Toads", "Salamanders")) 
     '
    wsBird(5) = Sheets(Array("Backyard Birds", "Ground Birds", "Birds of Prey", "Owls", "Seabirds", "Woodpeckers")) 
     '
    wsInsect(8) = Sheets(Array("Arachnids", "Beetles", "Butterflies", "Centipedes", "Bugs", "Flies", "Grasshoppers", 
"Dragonflies", "Social Insects")) 
     '
    wsMammal(5) = Sheets(Array("Carnivores", "Hoofed Mammals", "Marine Mammals", "Primates", "Rabbits", "Rodents")) 
     '
    wsReptile(3) = Sheets(Array("Crocodilians", "Lizards", "Snakes", "Turtles")) 
     '
    wsAnimals(30) = Sheets(Array("Freshwater Fish", "Saltwater Fish", "Sharks_Rays", "Sea Creatures", "Frogs_Toads",
"Salamanders", "Backyard Birds", "Ground Birds", _ 
    "Birds of Prey", "Owls", "Seabirds", "Woodpeckers", "Grasshoppers", "Dragonflies", "Social Insects", "Carnivores",
"Hoofed Mammals", "Marine Mammals", _ 
    "Primates", "Rabbits", "Rodents", "Crocodilians", "Lizards", "Snakes", "Turtles")) 
     '
    wsMaster(31) = Sheets(Array("Settings", "Freshwater Fish", "Saltwater Fish", "Sharks_Rays", "Sea Creatures",
"Frogs_Toads", "Salamanders", "Backyard Birds", _ 
    "Ground Birds", "Birds of Prey", "Owls", "Seabirds", "Woodpeckers", "Arachnids", "Beetles", "Butterflies", "Centipedes",
"Bugs", "Flies", "Grasshoppers", _ 
    "Dragonflies", "Social Insects", "Carnivores", "Hoofed Mammals", "Marine Mammals", "Primates", "Rabbits", "Rodents",
"Crocodilians", "Lizards", "Snakes", _ 
    "Turtles")) 
     '
    Let strComboValue = frmCtrAccess.ComboBox1.Value 
     '
    Select Case strComboValue 
    Case Is = "Fish" 
        wsFish.Visible = xlSheetVisible 
        wsFish.Select 
         '
    Case Is = "Amphibian" 
        wsAmphibian.Visible = xlSheetVisible 
        wsAmphibian.Select 
         '
    Case Is = "Bird" 
        wsBird.Visible = xlSheetVisible 
        wsBird.Select 
         '
    Case Is = "Insect" 
        wsInsect.Visible = xlSheetVisible 
        wsInsect.Select 
         '
    Case Is = "Mammal" 
        wsMammal.Visible = xlSheetVisible 
        wsMammal.Select 
         '
    Case Is = "Reptile" 
        wsReptile.Visible = xlSheetVisible 
        wsReptile.Select 
         '
    Case Is = "Animals" 
        wsAnimals.Visible = xlSheetVisible 
        wsAnimals.Select 
         '
    Case Is = "Master" 
        wsMaster.Visible = xlSheetVisible 
        wsMaster.Select 
    End Select 
End Sub 

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


Hi all,

Perhaps someone can help me..........I'd really appreciate it cause I'm going mental trying to figure this out. lol

I have the following code written in VBA.

Private Sub SupervisorComboBox_Change()
Sheets("Sheet2").Range("C1") = SupervisorComboBox.Value

I have a ComboBox that lists ten supervisors.
When the user chooses a supervisor from the ComboBox drop down list, the name gets stored on the second Sheet, in cell, "C1".

Now then.......there is a second ComboBox underneath this first one. This ComboBox lists each employee that works under the chosen supervisor. The employee's name gets stored in cell, "D1"

My question is this - If the user clears the screen (there is a ClearScreen button on the form) and wants to choose a new supervisor and a new employee, how do I get the program to store the new supervisor and employee name in cells, "C2" and, "D2", repsectively....?

Right now, if the user goes back and chooses new names from the ComboBoxes, the newly chosen name just gets REPLACED in the cells, "C1" and, "D1." (Which makes sense because the code specifically states to store in these cells.) HOWEVER.......I WANT EACH CLICK EVENT OF EACH COMBOBOX TO STORE THE DATA DOWN THE ROWS OF, "C' and, "D." I want the rows to get filled up each time a new name is picked from the ComboBox. (So each time a supervisor is chosen, for example, I want the chosen entry to get stored in, "C1" then in, "C2", then in, "C3", ect........Can anyone help me with the coding of this in VBA?

And when I clear the screen, how the heck do I get the program to also SAVE the entries that were stored in Sheet2??

If I can get specific code on this, I'd appreciate it. I'm really really lost on this.

Finally, I have this code for an einput box........
Sheets("Sheet2").Range("A1") = InputBox("Please enter your Employee ID", "Enter Employee ID")
This saves what the user types in the InputBox into cell, "A1." How the heck do I get it to save to, "A2", "A3", "A4", ext....when the user starts the program over again? Like with my ComboBox issue, it is REPLACING the value instead of storing the data down the row of column, "A."

Please help!!!!

I'm sorry this was so long. I'm just trying to explain it as fully as possible.
Pllease help, this is driving me mental. lol Thank you!

Chad

Hi.

I've followed MajP's answer on how to add a combobox to an 'inputbox' (userform) at this link:

http://www.tek-tips.com/viewthread.cfm?qid=1472240

However, I'm having trouble actually getting the information back into my macro. It keeps saying that the variable 'glblUser' is not recognised, in the Private Sub UserForm_QueryClose.

I've searched for hours and have now decided that I don't understand UserForm_QueryClose .

Can anyone tell me how to get the value the user selects in the combo box back into the macro. I've tried using a command button but that didn't work either.

Thanks in advance.

Mark.

Hello,

I have a sheet called "PartNumbers" and in Range A2:A I have all the part numbers, and in Range B2:B there is the plant location. I already have a filter on the combobox for part numbers that works fine, but now I need to apply a second one for when the user selects the option box for either "FNT" or "Press & Seal." How can I do this?

Here is the current code thanks to AAE, which filters column A and populates the combobox.
Any code is greatly appreciated, and thanks in advance.


	VB:
	
 PartList_Enter() 
    Sheets("PartNumbers").Select 
     
    Dim msg As String, strCriteria As String, strErrmsg As String 
    Dim lastrow As Long, rngList As Range, Ans As Variant 
     
    msg = "Please enter 3 digits, example: 123" 
    strErrmsg = "You have entered an invalid number. Would you like to try again?" 
     
    lastrow = Sheet8.Cells(Rows.Count, 1).End(xlUp).row 
top: 
    strCriteria = "=*" & Application.InputBox(msg, "Enter Three Digits", Type:=2) & "*" 
     
    If WorksheetFunction.CountIf(Sheet8.Range("A2:A" & lastrow), strCriteria) > 0 Then 
         
        With Sheet8 'sheet code name
             
             'turn off any filters before creating unique list
            If .AutoFilterMode = True Then .AutoFilterMode = False 
             
             ' apply auto-filter using the value from the input box
            .Range("A1").AutoFilter field:=1, Criteria1:=strCriteria, Operator:=xlAnd 
             
             ' define the range for the filtered values
            Set rngList = .Range("A2:A" & lastrow).SpecialCells(xlCellTypeVisible) 
             
             ' clear the paste range to receive fresh content
            .Range("G1").CurrentRegion.Resize(, 1).ClearContents 
            rngList.Copy .Range("G1") 
        End With 
         
         ' set the row source of the combo box the refernce the filtered list
        With Me.PartList 
            .RowSource = Sheet8.Range("G1").CurrentRegion.Address 
        End With 
        Set rngList = Nothing 
         '    Exit Sub
    Else 
        Ans = MsgBox(strErrmsg, vbYesNo + vbQuestion) 
        Select Case Ans 
        Case vbYes: Goto top: 
Case vbNo: 'do nothing - exit sub
        End Select 
    End If 
     
End Sub 

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


I have this code snipit and it works well,
How can I change it to work with a combobox/listbox.
I have a list of 24 fixed items that I want the user to choose the one they want.
I dont want to have the items in the spreadsheet cells, instead I want to have the list done in VB

Thanks
Mike


	VB:
	
 
myRange = Application.InputBox(prompt:="Enter your search criteria:", Type:=2) 
If myRange = "" Then Exit Sub 
With ActiveSheet.QueryTables.Add(Connection:= _ 
    "URL;http://clusty.com/search?query=" & myRange, Destination:= _ 
    Range("A2")) 
    .WebSelectionType = xlEntirePage 
    .WebFormatting = xlWebFormattingAll 
    .WebPreFormattedTextToColumns = True 
    .WebConsecutiveDelimitersAsOne = True 
    .WebSingleBlockTextImport = False 
    .WebDisableDateRecognition = False 
    .WebDisableRedirections = False 
    .Refresh BackgroundQuery:=False 
End With 

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


1.i want to put combobox on a userform.I know that before writing "cases" in combobox i should add items in the combobox.I also know that I should additems before showing userform(userform1.show).but i do not know where should I additems.so I created a function called userform1_initialize and wrote

combobox1.additem "7"
combobox1.additem "14"
combobox1.style = fmstyledrodownlist
combobox1.boundcolumn = 0
but it returns the run time error `424`:object required what should I do?
2.how can I make an inputbox like something attached to the userform?

Hi all,

I've bene having a particular bad friday and can't get my head around a simple problem.

I have a combobox linked with a dynamic range (=OFFSET(LISTS!$A$44,0,0,COUNTA(LISTS!$A:$A),1)). The user is prompted to add his name in an input box when his name is not in the list. The named range should update but i can't get it to work.
The combobox is on another sheet then the list, but that shouldn matter, tight?

Private Sub ComboBox2_Change()
Dim Target As String
If ComboBox2.Value = "New user" Then
Target = InputBox("Enter name", "Name")
If Target = "" Then Exit Sub
Range("Originator").AddItem Target

End If
End Sub

I may be totally off here as I am a true newbie, please help!

I have a workbook that contains 9 worksheets. Four of the worksheets have the same row lables in column B and must always be the same. Three of the worksheets are fed from the 4th sheet so that the integrity of the lables is maintained.

The user can change the lable value to meet their needs and they can insert or delete rows (within limits) as they see fit.

The attached macro "Sub Delete_Row_All_Sheets()" works fine when I run it from Debug (F8).

However, when I run it from Forms.ComboBox the macro returns to the "y = Application.InputBox("Enter The Row Number You Wish To Delete", _" screen. If I select cancel, the results I anticipated occur but I don't want the user to have to assume this will happen.

Why does the macro return to this screen when executed from the ComboBox but not when executed from Debug?

Private Sub ComboBox1_Change()
If ComboBox1.ListIndex > -1 Then Run ComboBox1
ComboBox1.Object.Value = ""
End Sub

Sub Delete_Row_All_Sheets()

    Dim wkshts As Variant
    Dim y As Integer
    Dim sht As Variant
    
  '  Tell me where to delete the row
        Sheet6.Select ' Go to Annual Budget sheet
reselect:
                
        y = Application.InputBox("Enter The Row Number You Wish To Delete", _
        Type:=1) 'enter 16 to Delete a row; the row will be deleted_
                 'all other rows push up 1 row As well.
           
           If y = 0 Then Exit Sub
                     
           If y < 7 Then MsgBox ("You Can't Delete Rows In Headers")
           If y < 7 Then GoTo reselect
           
           If Range("A" & y).Value <> Empty Then MsgBox ("You Can't Delete Rows In Catagory")
           If Range("A" & y).Value <> Empty Then GoTo reselect
          
    ' Are you sure?
      If MsgBox("Are You Sure You Wish To Delete At Row " & y & " For ALL Sheets?", _
        vbYesNo, "Delete Row On ALL Sheets") = vbNo Then Exit Sub  
    
        Application.ScreenUpdating = False  'Don't update the screen yet
   
    ' For all sheets selected in Array Delete a line at range y
       
  wkshts = Array(3, 2, 4, 5)
  For Each sht In wkshts
    
    ThisWorkbook.sheets(sht).Select
    ThisWorkbook.sheets(sht).Unprotect
        Rows(y).Select
        Rows(y).delete
    ThisWorkbook.sheets(sht).protect
    Next sht
    
        Application.ScreenUpdating = True  'Update the screen


  Sheet6.Select ' Go to Annual Budget sheet
End Sub
I am using Excel 2007 on worksheets in .xls compatability mode.

This is my first posting. Your "How To Videos" appear to be not working so I could not watch them to see proper protocol. Hope I didn't mess up too bad.

Thanks for your help.

John

I have a Userform that unhides Worksheets - the Userform works fine. I woud like to call for a password if the User chooses a specific Option.
I have tried:
Private Sub CboMainframe_Change()
    
      Dim PW As String
      Const PWOK As String = "Password"
    
      If CboMainframe.Text <> "Solutions" Then
      Exit Sub
      ElseIf CboMainframe.Text = "Solutions" Then
        PW = InPutBox("Please enter Password", "Solutions Password Required")
        Select Case PW
         Case vbNullString
         MsgBox "User cancelled"
         ThisUserForm.Close False
          Case Is <> PWOK: MsgBox "That's not correct"
         ThisUserForm.Close False
         Case Else: MsgBox "Password OK"
         End Select
   
        End If
             
      
End Sub
The idea: if the ComboBox is changed to Solutions - then an inputbox pops up asking for pw...
Currently the userform works like normal (never asks for PW).

Anyone see what I am doing wrong?

I have this section of code:

Public Sub NewEntry()

'Declare Variables
Dim Customer, Location, Program, Description, Analysis, Sales As String
Dim smplLocation, Labtech, smplVolume, sht_year, project_number As String
Dim datesmpld_string, daterec_string As String
Dim datedisposal, datesmpld, daterec As Date
Dim iRow, i, number_labels, n As Long

'Create Input Boxes
'Customer = CStr(InputBox("Enter Customer Name"))
Description = CStr(InputBox("Enter Sample Description"))
Analysis = CStr(InputBox("Enter Analysis Required"))
Sales = CStr(InputBox("Enter Sales/Contact Person"))
smplLocation = CStr(InputBox("Enter Location of sample"))
As you can see right now the Customer name is just an input box, what I would like to do is have a combobox popup (with the list coming from another worksheet) and have the user select from that list.

Private Sub
UserForm_Initialize()
    Dim rngList As Range
     
    Set rngList = Sheets("Database").Range("A4", Range("A65536").End(xlUp))    
Me.ComboBox1.Clear
    Me.ComboBox1.RowSource = rngList.Address
     
End Sub
I have no clue how to make it so that when i hit the "ok" button on the userform with the combobox to assign the value chossen to the variable "Customer". Any ideas? Thanks in advance!

Help! I'm new at working with macros and forms in excel and I'm having a problem that I can't figure out.

I currently have the below code for one of my comboboxes.

Private Sub ComboBox_IS_Prev_Change()
If ComboBox_IS_Prev.Value = "Other" Then
other_name = Application.InputBox(prompt:="Enter Name of Previous Provider", Type:=2)
Range("E35").FormulaR1C1 = other_name
Else
Range("E35").FormulaR1C1 = ""
End If
End Sub

This macro works fine but for some reason when I do a Save As on the file, this function is running and causing the inputbox to be displayed. And the inputbox isn't displayed just once, it's displayed twice before the file will save.

Thanks!
Torri

Hey

I have a userform which is generated dynamically depending on how many columns are in a sheet.
It is given a label and a textbox for each column of data.

Next a Sub goes through the columns and checks for data validation, changing the userform so that it reflects that validation.

If the cell is validated by a list (validation.type=3) the textbox is replaced by a combobox which is populated with the values in the list.

When I populate the combobox with values they are adding to the list(I know this cause setting ListIndex to 1 causes the 2nd value I added to show as default)

When I click on the drop-down button the list appears empty. Below is my code, any ideas?


	VB:
	
 Worksheet) 
    Dim Form As VBComponent 
    Dim Ctl As Control 
    Dim Table As String 
    Dim I As Integer 
    Dim Columns As Integer 
     
    Table = Sh.Name 
    Set Form = ThisWorkbook.VBProject.VBComponents(Table & "Form") 
     
    For Each Ctl In Form.Designer.Controls 
        If TypeName(Ctl) = "TextBox" Then 
            Columns = Columns + 1 
        End If 
    Next Ctl 
     
    For I = 1 To Columns 
        Dim Rng As Range 
         
         
        Set Rng = Intersect(Sh.Cells(2, I), Sh.Cells. _ 
        SpecialCells(xlCellTypeAllValidation)) 
         
         
        If Not Rng Is Nothing Then 
            Select Case Sh.Cells(2, I).Validation.Type 
Case 7: 
                 'Add data validation code into Exit sub for corresponding control
                With Form.CodeModule 
                    .InsertLines .CountOfLines + 1, vbCrLf 
                    .InsertLines .CountOfLines + 1, "Private Sub CTL" & I & "_Exit(ByVal Cancel As MSForms.ReturnBoolean)" 
                    .InsertLines .CountOfLines + 1, "    If ExitOff Then" 
                    .InsertLines .CountOfLines + 1, "        ExitOff = True" 
                    .InsertLines .CountOfLines + 1, "        Exit Sub" 
                    .InsertLines .CountOfLines + 1, "    End If" & vbCrLf 
                     
                    .InsertLines .CountOfLines + 1, "    If NOT(EVALUATE(""COUNTIF(" & Table & "!$" & _ 
                    Left(Sh.Cells(2, I).Address(1, 0), InStr(1, Sh.Cells(1, 45).Address(1, 0), "$") - 2) _ 
                    & ":$" & _ 
                    Left(Sh.Cells(2, I).Address(1, 0), InStr(1, Sh.Cells(1, 45).Address(1, 0), "$") - 2) _ 
                    & ", "" & CTL" & I & ".Value & "")=0"")) Then" 
                    .InsertLines .CountOfLines + 1, "        MsgBox (""TAS Ref. Already exists"")" 
                    .InsertLines .CountOfLines + 1, "        Cancel = True" 
                    .InsertLines .CountOfLines + 1, "        With CTL" & I 
                    .InsertLines .CountOfLines + 1, "            .SelStart = 0" 
                    .InsertLines .CountOfLines + 1, "            .SelLength = Len(.Text)" 
                    .InsertLines .CountOfLines + 1, "        End With" 
                    .InsertLines .CountOfLines + 1, "    End If" 
                    .InsertLines .CountOfLines + 1, "End Sub" 
                End With 
                 
                 
Case 3: 
                Dim NRng As Range 
                Dim CCtl As Combobox 
                 
                 'Set CCtl to newly created combobox
                Set CCtl = Form.Designer.Controls.Add("forms.ComboBox.1") 
                 
                 'Set properties of Combobox
                With Form.Designer.Controls("CTL" & I) 
                    CCtl.Top = .Top 
                    CCtl.Left = .Left 
                    CCtl.Width = .Width 
                    CCtl.Height = .Height 
                End With 
                 
                 'Delete textbox it is replacing and transfer name
                Form.Designer.Controls.Remove ("CTL" & I) 
                CCtl.Name = "CTL" & I 
                 
                 'Add empty item
                CCtl.AddItem vbNullString 
                 
                 'For every cell in validation list range
                For Each NRng In Range(Right(Sh.Cells(2, I).Validation.Formula1, Len(Sh.Cells(2, I).Validation.Formula1) -
1)) 
                     
                     'Add Item to combobox list
                    CCtl.AddItem NRng.Value 
                Next NRng 
                 
                 'This was to test if item was actually on list
                CCtl.ListIndex = 1 
            End Select 
        End If 
    Next I 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
In the code above it is the code handling case 3 which adds a combobox to the form on top of a textbox, deletes the textbox and then populates the combobox

Can anyone see anything wrong with this?

Hey guys,

I hope anyone out there has an idea for this because, although I'm fairly good with Excel (not VBA, sadly), I think this time I've bitten more than I can chew .

I'm trying to put together a dashboard in Excel 2010, for a HR project I'm working on. For this, I'm starting with two employee summaries, based on showing the employees as headcount or FTEs, for 3 different companies. Each list contains data related to employee's gender, level (management or staff), age bracket, level of education, status and company ID (Comp1, Comp2 or Comp3). In the FTE summary, I also have the FTE correspondence, in a separate column.

In the actual dashboard, I have 7 comboboxes that I would like to use as criteria to summarize the data in the lists and pass the result (count for the headcount list, sum of FTE for the FTE list) to a textbox. The criteria are:

1. The way the number of employees is viewed: Headcount or FTE;
2. Company: All; Comp1; Comp2; Comp3
3. Level of education: All; high school; university degree; masters' degree; PhD;
4. Position level: All;Director; manager; supervisor; staff;
5. Employee status: All; active; inactive
6. Gender: All; Male; Female.
7. Age Bracket: All; 45.

The default value for comboboxes 2-7 is "All".

I think that the corresponding list (headcount or FTE) should be filtered progressively based on the criteria that the user selects by changing the combobox values, but other than that, I'm lost

The result of the count/sum should go to sheet "Calcs", cell I12 (if the data is viewed as headcount) or J12 (if the data is viewed as FTE). From there, I'll take the result and pass it to the textbox.

Does anyone have an idea as to where I should begin? I've been working on this for the past week, trying and discarding option after option, and I still can't figure it out .

Sorry for the long post, and if you need a sample workbook, please let me know.

Thanks for your opinions and help,

Radu

Hi Guys I need help to create a dynamic chart by using a combobox. ex: if I select abc from combobox all list included in abc category will only be shown in the chart, the difficult part is abc has ex 10 rows while def has 19 and so on.... is it also possible to add a scroll bar in that chart? I included the data

It's been quite some time since I got involved in any vba and find myself struggling to relearn what I once knew. That said, what little I did know wasn't THAT extensive -- I've spent days trying to adapt various examples to my needs with little, if any, success.

My initial thought/goal was to script some vba to concatenate a number of ranges and copy the output to two sheets, but that proved too troublesome due to the number of columns columns and the amount of code/skill needed, so I gave up on that pipedream. I've decided instead, now, to run formulas across several hidden worksheets linked to another workbook containing the data and then copy the concatenated values (it's only five columns, but needs to be selectable by row/range of rows) from the hidden worksheet to the two other worksheets, one clears the data each time the script is run, the other to find the last line and drop the same values for a historical log.

I've had only one of the last eighteen attempts work halfway; Several times I was able to run it once, but upon running the script again, it would error out. I was bound and determined to not let this beat me, but I've wasted more time at work and two and a half days of my three day weekend searching and at this point, I'm fresh out of steam -- and I hate myself for it.

Anyone have any suggestions on how to approach this?

Edit: Incidentally, the only other requirement I have, given my second choice/lazy approach, is that another worksheet remain in focus when the script is run to use, as a cell reference, a copy of the linked workbook data. Optimally, it would not be selectable, but I can work that out later...

Here's what I''ve managed to piece together. Sorry for the hatchet job. If anyone can assist, it would be greatly appreciated.


	VB:
	
 Worksheet) 
    On Error Resume Next 
    Lastrow = sh.Cells.Find(What:="*", _ 
    After:=sh.Range("A1"), _ 
    Lookat:=xlPart, _ 
    LookIn:=xlValues, _ 
    SearchOrder:=xlByRows, _ 
    SearchDirection:=xlPrevious, _ 
    MatchCase:=False).Row 
    On Error Goto 0 
End Function 
 
Sub userin() 
     
     'Dim uiRange As Range
     'Dim inputValues As Variant
     'On Error Resume Next
     'Set uiRange = Application.InputBox("Input a range.", Type:=8)
     'On Error GoTo 0
     'inputValues = uiRange.Value
     'Rem manipulate inputValues
     'ActiveCell.Resize(uiRange.Rows.Count, uiRange.Columns.Count).Value = inputValues
     '
    Dim vArrIn As Range ' input range
    Dim vArrOut As Range ' output range
    Dim oStart As String ' Starting cell of output range
    Dim oEnd As String ' end cell of output range
    Dim columns1 As Integer ' number of columns in array
    Dim rows1 As Integer ' number of rows in array
     
    Set vArrIn = Application.InputBox("Select Range", Type:=8) 
    rows1 = vArrIn.Rows.Count 
    columns1 = vArrIn.Columns.Count 
    oStart = ActiveCell.Address 
     'oEnd = ActiveCell.Offset(rows1 - 1, columns1 - 1).Address
     '

Dear all,
I'd like to have a combobox content of two data but showing only one data. When it's chosen then data which is not shown appear.
For example: combo box shows employees' name but when it is selected then his/her badge is shown at "B1".
The following is what I found, but it shown non unique data


	VB:
	
 
Sheets("Sheet1").ComboBox1.Clear 
Myarray = Worksheets("Sheet2").Range("A2:B19") 
With Sheets("Sheet1").ComboBox1 
    .Clear 
    .ColumnCount = 2 
    .BoundColumn = 1 
    .List() = Myarray 
    .ColumnWidths = "0;" 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Your assistance is higly appreciated
Alle2233

I have a Table that shows different Circuit Amps for HVAC Units.

I am trying to make a Userform that allows me to select the different options and such and output the Model Number, Weight, and Circuit Arrangement.

What I want is a way to show what Circuit Breakers would be needed for each model based on the selection in ComboBox1 on my form.
Here is my dilemma: Each model has two breakers, and so I have a table that is 7x20 and I only have 7 base models.

Here is my table:

BASE MODEL ELECTRIC HEAT 0 = NONE 36 = 3.6 kw 40 = 4 kw 50 = 5 kw 60 = 6 kw 80 = 8 kw 90 = 9 kw 100 = 100 kw 120 = 12 kw 150 = 15 kw CIRCUIT 1 CIRCUIT 2 CIRCUIT 1 CIRCUIT 2 CIRCUIT 1 CIRCUIT 2 CIRCUIT 1 CIRCUIT 2 CIRCUIT 1 CIRCUIT 2 CIRCUIT 1 CIRCUIT 2 CIRCUIT 1 CIRCUIT 2 CIRCUIT 1 CIRCUIT 2 CIRCUIT 1 CIRCUIT 2 CIRCUIT 1 CIRCUIT 2 AVPA24ACA 30 N/A N/A N/A 30 N/A 30 N/A 40 N/A 50 N/A N/A N/A 60 N/A N/A N/A N/A N/A AVPA30ACA 40 N/A N/A N/A 40 N/A 40 N/A 40 N/A 50 N/A N/A N/A 60 N/A 40 50 40 60 AVPA36ACA 40 N/A N/A N/A 40 N/A 40 N/A 40 N/A 50 N/A N/A N/A 60 N/A 40 50 40 60 AVPA42ACA 50 N/A N/A N/A N/A N/A 50 N/A N/A N/A N/A N/A N/A N/A 60 N/A 50 50 50 60 AVPA48ACA 50 N/A N/A N/A N/A N/A 50 N/A N/A N/A N/A N/A N/A N/A 60 N/A 50 50 50 60 AVPA60ACA 60 N/A N/A N/A N/A N/A 60 N/A N/A N/A N/A N/A N/A N/A 60 N/A 60 50 60 60 AVPA72ACA 60 N/A N/A N/A N/A N/A 60 N/A N/A N/A N/A N/A N/A N/A 60 N/A 60 50 60 60
What I need is a way to show the two circuits based on the selection in two ComboBoxes: The Base Model and the Heating Selection.

Is there any way at all to do this?

I have a combobox on a word document that gets its values from an excel file.


	VB:
	
"" 
    Combobox1.AddItem objExcel.Range ("A" & lngRow) 
    lngRow = lngRow +1 
Loop 

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

I would however like to be able to view columns A & B (bind to A) on the combo. I have tried
.Range ("A" & lngRow & ":B" & lngRow) but this does not work. I get error 1004 'Application-defined or object-defined error'

Any help

I'm guessing this will be a snap for most of you, I've been stumbling through VBA for a few weeks now and have found many solutions on Ozgrid...so thanks to all those who've contributed your expertise!

I am creating a tabbed userform. The first tab is used to enter a person's demographics into a registry with first name, middle initial and last name as distinct fields input into a dynamic range. The subsequent tabs have comboboxes whose RowSource is the dynamic range. I have displayed all three columns in the drop down list just fine however when a person is selected only the first name displays in the textbox. I would like the person's full name to display in the box once selected. I am trying to do this as elegantly as possible and seems there should be a better solution that creating a function to concatenate the fields and populate a single row on a hidden sheet to use as the RowSource.

Many Thanks!

ComboBox Triggers When Cell is Deleted.xlsm
Worksheet consists of a ComboBox with a ListFillRange of B6:B10 and no LinkedCell.
Deleting any cell, even without disturbing the ListFillRange, triggers the ComboBox's Change Event. Why? Worksheet is attached.

Hi everyone,

I'm new here and also in VBA. Maybe you guys can help me out.

I've been trying to create a Form where I can get certain sheets from a main Spreadsheet which has the prices and discounts of each product separately in each sheet to a new spreadsheet. I've made the first step where I've could copy and paste the sheets from the Main Spreedsheet by selecting in the combobox.

UserForm.jpg

But there are two problems, 1) if I include twice the same model, the spreadsheet only copy and paste once the spreadsheet and 2) if I left one combobox empty, it appears an error saying it can't be generated.

Here is the code:


	VB:
	
 UserForm_Initialize() 
    Me.cboModel1.RowSource = "Sheet1!A2:A5" 
    Me.cboModel2.RowSource = "Sheet1!A2:A5" 
    Me.cboModel3.RowSource = "Sheet1!A2:A5" 
     
End Sub 
 
Private Sub btnGenerate_Click() 
     
    Dim plan1, plan2, plan3 As String 
     
    plan1 = CStr(Me.cboModel1.Value) 
    plan2 = CStr(Me.cboModel2.Value) 
    plan3 = CStr(Me.cboModel3.Value) 
     
     ' Copy and paste the selected models to a new workbook
    Sheets(Array(plan1, plan2, plan3)).Select 
    Sheets(plan1).Activate 
    Sheets(Array(plan1, plan2, plan3)).Copy 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I've searched in the forum and I didn't find anything similar, if someone could help I appreciate.

Thx,

The spreadsheet:
Price Form v0.02.xls

I'm not sure if this is even possible/the best way of doing this. I have spent a long time trying to figure this out and can't find an answer so suspect there is a better way.

I need a userform that will allow the user to pick a record based on date and name (2 columns in the db worksheet please see attached)
Once the selection has been made I have a commandbutton which needs to be clicked and it will fill in a report (Report worksheet) which in the end will be printed, but that isn't the issue.

I have populated the combobox with the date and name but am struggling to populate the Report. I had been searching for the date to select the row which was required, but it dawned on me that there
may be 2 records with the same date. The same goes for the name. If I continue down this route should I be adding an unique ID for each record and searching for that?
or alternatively is the combobox the correct way?

In the end there may be 100 records + (not many more) so having a list to choose from is the easiest for the users.

Any thoughts would be appreciated.

Thanks

I want a label to appear if there are certain conditions on 4 comboboxes. However, I do not know where to place the code (in which private sub) to make it work.

Below you can find the code:


	VB:
	
 
    Label10.Visible = False 
Else: Label10.Visible = True 

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