Posts by Blazingspleen

Blazingspleen · Nov 7th 2019

I thought about moving it, but I think it needs to be in view so there is no going back and forth...

Blazingspleen · Nov 6th 2019

Currently everything works however; the proposal sheet needs to be a little more flexible. some of the companies i bid to, request a breakdown of costs. Then i have to add rows... and then i have to change the checkbox assignments.

After i posted an idea popped into my head: can a user form be created? A form that contains all check boxes next to a text box's, and after making the selection it is similarly applied to the textbox on my sheet?

Blazingspleen · Nov 5th 2019

here is a copy of my workbook

Blazingspleen · Nov 4th 2019

My bids contain a lot of the same items, and to reduce time and typing i have a column that contains the item, a column for "includes", and a column for "excludes". i currently use a checkbox in the corresponding column to send the item to a text box for combined includes and excludes. i use the following:

Code

Private Sub CommandButton1_Click()


TextBox1.Text = ""


If CheckBox1.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R37").Value & " - " & Range("S37").Value & ", "
End If


If CheckBox2.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R38").Value & " - " & Range("S38").Value & ", "
End If


If CheckBox3.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R39").Value & " - " & Range("S39").Value & ", "
End If


If CheckBox4.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R40").Value & " - " & Range("S40").Value & ", "
End If


If CheckBox5.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R41").Value & " - " & Range("S41").Value & ", "
End If


If CheckBox6.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R42").Value & " - " & Range("S42").Value & ", "
End If


If CheckBox7.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R43").Value & " - " & Range("S43").Value & ", "
End If


If CheckBox8.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R44").Value & " - " & Range("S44").Value & ", "
End If


If CheckBox9.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R45").Value & " - " & Range("S45").Value & ", "
End If


If CheckBox10.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R46").Value & " - " & Range("S46").Value & ", "
End If


If CheckBox11.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R47").Value & " - " & Range("S47").Value & ", "
End If


If CheckBox12.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R48").Value & " - " & Range("S48").Value & ", "
End If


If CheckBox13.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R49").Value & " - " & Range("S49").Value & ", "
End If


If CheckBox14.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R50").Value & " - " & Range("S50").Value & ", "
End If


If CheckBox15.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R51").Value & " - " & Range("S51").Value & ", "
End If


If CheckBox16.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R52").Value & " - " & Range("S52").Value & ", "
End If


If CheckBox17.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R53").Value & " - " & Range("S53").Value & ", "
End If


If CheckBox18.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R54").Value & " - " & Range("S54").Value & ", "
End If


If CheckBox19.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R55").Value & " - " & Range("S55").Value & ", "
End If


If CheckBox20.Value = True Then
    TextBox1.Text = TextBox1.Text & Range("R56").Value & " - " & Range("S56").Value & ", "
End If




TextBox2.Text = ""


If CheckBox21.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R37").Value & " - " & Range("S37").Value & ", "
End If


If CheckBox22.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R38").Value & " - " & Range("S38").Value & ", "
End If


If CheckBox23.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R39").Value & " - " & Range("S39").Value & ", "
End If


If CheckBox24.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R40").Value & " - " & Range("S40").Value & ", "
End If


If CheckBox25.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R41").Value & " - " & Range("S41").Value & ", "
End If


If CheckBox26.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R42").Value & " - " & Range("S42").Value & ", "
End If


If CheckBox27.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R43").Value & " - " & Range("S43").Value & ", "
End If


If CheckBox28.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R44").Value & " - " & Range("S44").Value & ", "
End If


If CheckBox29.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R45").Value & " - " & Range("S45").Value & ", "
End If


If CheckBox30.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R46").Value & " - " & Range("S46").Value & ", "
End If


If CheckBox31.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R47").Value & " - " & Range("S47").Value & ", "
End If


If CheckBox32.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R48").Value & " - " & Range("S48").Value & ", "
End If


If CheckBox33.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R49").Value & " - " & Range("S49").Value & ", "
End If


If CheckBox34.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R50").Value & " - " & Range("S50").Value & ", "
End If


If CheckBox35.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R51").Value & " - " & Range("S51").Value & ", "
End If


If CheckBox36.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R52").Value & " - " & Range("S52").Value & ", "
End If


If CheckBox37.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R53").Value & " - " & Range("S53").Value & ", "
End If


If CheckBox38.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R54").Value & " - " & Range("S54").Value & ", "
End If


If CheckBox39.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R55").Value & " - " & Range("S55").Value & ", "
End If


If CheckBox40.Value = True Then
    TextBox2.Text = TextBox2.Text & Range("R56").Value & " - " & Range("S56").Value & ", "
End If
End Sub

Display More

I need add and subtract rows from the sheet and i have to reset the cell assignments every time. I am open to any suggestions on how to change the code, and/or any other suggestions to do the same.

Blazingspleen · Nov 4th 2019

type the first few letters and it will reduce the list to the letters you type. example:
ample
apple
apply
appendicitis
appendix

type A and it shows all in the above list, type am and only Ample will be shown.

Blazingspleen · Oct 16th 2019

I am creating a bid list, that may have several contractors bidding. I want to search a list of hundreds of contractors and be able to select 1 to 10(or more) from the same list.

I found code that is a worksheet change function, that allows you to make multiple selections from a drop down list:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
' Developed by Contextures Inc.
' www.contextures.com/excel-data-validation-multiple.html
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler


On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler


If rngDV Is Nothing Then GoTo exitHandler


If Intersect(Target, rngDV) Is Nothing Then
   'do nothing
Else
  Application.EnableEvents = False
  newVal = Target.Value
  Application.Undo
  oldVal = Target.Value
  Target.Value = newVal
    If oldVal = "" Then
      'do nothing
      Else
      If newVal = "" Then
      'do nothing
      Else
      Target.Value = oldVal _
        & ", " & newVal
'      NOTE: you can use a line break,
'      instead of a comma
'      Target.Value = oldVal _
'        & Chr(10) & newVal
    End If
  End If
End If


exitHandler:
  Application.EnableEvents = True
End Sub

Display More

is there a way to do this and make a drop down searchable? I am open to alternative methods, this code doesn't have to be used.

Blazingspleen · Feb 13th 2018

I have a table with 10,000 + items. to filter i added 5 columns: Paving, Iowa, Nebraska, Non-DOT, and Rebar. The new columns will have Y/N, and some items will have multiple Y's and/or N's. I need a dropdown with the new columns, and i need to filter the list by the Y in the column. I split the main list into several smaller lists thinking i could use datavalidation to select a specified list, but i can't figure out how. please see the attached. the main reason i want it this way is to simplify estimation for others: select table then select item, populate price, multiply quantity-- all on one line, then copy that line down and repeat. the result would be a list/table i could then import to a form that we send to a customer.

Posts by Blazingspleen

LAZY TYPIST: use a column of predetermined items to fill a text box at need.

LAZY TYPIST: use a column of predetermined items to fill a text box at need.

LAZY TYPIST: use a column of predetermined items to fill a text box at need.

LAZY TYPIST: use a column of predetermined items to fill a text box at need.

Searchable drop down list with multiple selections in the same cell

Searchable drop down list with multiple selections in the same cell

Use a dropdown to filter a table