If a number>1 is entered, I need an input box to appear and enter the answer in a cell

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,209
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have a table on my spreadsheet called css_quote that records information about jobs, where column F records the staff required. If anything is entered in this cell that is greater then 1, I need an input box to appear asking how many cars are needed. I then need this number put into column L for the row.

This is my worksheet_change event for the sheet that has the table. Can someone help me with the vba to achieve the above please?

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Quoting.Unprotect Password:=ToUnlock
      Dim ans As String
    
    'code to enter allow organisation to be entered if other is selected
        If Not Intersect(Target, Me.Range("B7")) Is Nothing Then
            If LCase(Me.Range("B7").Value) = "other" Then
                ans = InputBox("Please enter organisation.", , Me.Range("B7").Value)
                If ans <> "" Then
                    Range("B7").Value = ans
                End If
            End If
        End If
    
        If Selection.Count = 1 Then
     
            'If Not Intersect(Target, Range("B7")) Is Nothing Then
           '     Workbooks.Open ThisWorkbook.Path & "\" & "Client_list.xlsm"
           ' End If
        End If
      If Target.Count > 1 Or IsEmpty(Target) Then Exit Sub
      '
      On Error GoTo App_Events
      If Not Intersect(Target, Range("A:A,B:B")) Is Nothing Then
    
        Select Case Target.Column
          Case 1
            If Target.Value < Date Then
              If MsgBox("This input is older than today. Are you sure that is what you want?", vbYesNo) = vbNo Then
                Target.Value = ""
              End If
            End If
          Case 2
            If LCase(Target.Value) = LCase("Activities") Then
              Do
                ans = InputBox("Please enter the Activities cost." & _
                  vbCrLf & "************************************" & vbCrLf & _
                  "To change an activity cost, select Activities from the Service list again.")
                If ans <> "" Then
                  Cells(Target.Row, "M") = ans
                  Exit Do
                Else
                  MsgBox ("You must enter a Activities cost.")
                End If
              Loop
            End If
        End Select
      End If

'    If Selection.Count = 1 Then
'        If Not Intersect(Target, Me.ListObjects("CSS_Quote").ListColumns(1).Range) Is Nothing Then
'            MsgBox "hello"

'        End If
'    End If


    
      
App_Events:
      Application.EnableEvents = True
    Quoting.Protect Password:=ToUnlock

End Sub
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,209
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Sweet, I worked out how to do it. I dimmed cars as long and I added this code to the end of my worksheet_change event.

VBA Code:
    If Selection.Count = 1 Then
        If Intersect(Target, Me.ListObjects("CSS_Quote").ListColumns(6).Range) > 1 Then
            Do
                cars = InputBox("Please enter how many cars are required.")
                    If cars > 1 Then
                        Cells(Target.Row, "L") = cars
            Exit Do
                    Else
                        Cells(Target.Row, "L") = 1
                    End If
            Loop
        End If
    End If
 
Solution

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,164
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
I think "cars" should be dimmed as Integer, not that it really matters ... :cool:
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,164
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

Have a look here.....you could prob use Byte, but I alwasy use Integer.....I doubt anyone will need more than 32767 cars for the day... :ROFLMAO:

Table of All The VBA Data Types
Data TypeStoredRange of Values
Byte​
1 Byte​
0 to 255​
Integer​
2 Bytes​
-32,768 to 32,767​
Single​
4 Bytes​
-3.402823E38 to -1.401298E-45 for negative values, 1.401298E-45 to 3.402823E38 for positive values​
Long​
4 Bytes​
-2,147,483,648 to 2,147,483,648​
Double​
8 Bytes​
-1.79769313486232e+308 to -4.94065645841247E-324 for negative values, 4.94065645841247E-324 to 1.79769313486232e+308 for positive values.​
Decimal​
14 Bytes​
+/-79,228,162,514,264,337,593,543,950,335 for no decimal points,+/-7.9228162514264337593543950335 for 28 places to the right of the decimal​
Date​
8 Bytes​
January 1, 100 to December 31, 9999​
Currency​
8 Bytes​
-922,337,203,685,477.5808 to 922,337,203,685,477.5807​
String (variable length)​
10 bytes added to the string length​
0 to 2 billion characters​
String (fixed length)​
string length​
1 to approximately 65,400​
Object​
4 Bytes​
Object in VBA​
Boolean​
2 Bytes​
True or False​
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,209
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
With this problem Michael, I just realised that if the number entered in column 6 is 1, I need a 1 entered in column L. I tried to put an else below the initial if statement but it didn't work. Could you help me please?

VBA Code:
    If Selection.Count = 1 Then
        If Intersect(Target, Me.ListObjects("CSS_Quote").ListColumns(6).Range) > 1 Then
            Do
                cars = InputBox("Please enter how many cars are required.")
                    If cars > 1 Then
                        Cells(Target.Row, "L") = cars
                        Exit Do
                    Else
                        Cells(Target.Row, "L") = 1
                    End If
            Loop
        Else
            Intersect(Target, Me.ListObjects("CSS_Quote").ListColumns(12).Range) = 1
        End If
    End If
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,209
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Sweet, I worked it out. Thanks anyway Michael

VBA Code:
    If Selection.Count = 1 Then
        If Intersect(Target, Me.ListObjects("CSS_Quote").ListColumns(6).Range) > 1 Then
            Do
                cars = InputBox("Please enter how many cars are required.")
                    If cars > 1 Then
                        Cells(Target.Row, "L") = cars
                        Exit Do
                    Else
                        Cells(Target.Row, "L") = 1
                    End If
            Loop
        Else
            If Intersect(Target, Me.ListObjects("CSS_Quote").ListColumns(6).Range) = 1 Then
                Cells(Target.Row, "L") = 1
            End If
        End If
    End If
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,209
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
About post 5, I think I read somewhere about if you use integer, vba has to convert it to long for calculations, meaning it would use even more memory.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,164
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Only if you wanted to do a calculation on thar variable. As you won't be dividing / multiplying it by fractions, it will always be an Integer

Rich (BB code):
Integer and Long are both used to store whole numbers. They would be no good if you wanted divide 10 by 3, say. If you want a remainder, you'll need a different variable type.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,814
Messages
5,638,493
Members
417,029
Latest member
lingx86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top