input value default by 1.

rapitorres

New Member
Joined
Oct 5, 2017
Messages
34
Office Version
  1. 2010
Platform
  1. Windows
Hi anyone can help me?
I've been having difficulty in making the input box value default by "1"

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As String
ans = InputBox("Enter Quantity")
Target.Offset(, 4).Value = ans

I want to make the pop-up box value default by 1. and still can be edited by the user.
is that possible? or do I need to take another route for this? the line of code above is working fine.
I just want the value to default by 1.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,882
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
ans = InputBox("Enter Quantity", , "1")
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,790
Office Version
  1. 2019
Platform
  1. Windows
Hi,
try this update to your code

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ans As Variant

    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
        
        Do
            ans = InputBox("Enter Quantity", "Enter Quantity", 1)
            'cancel pressed
            If StrPtr(ans) = 0 Then Exit Sub
        Loop Until Val(ans) > 0
        
        Target.Offset(, 4).Value = Val(ans)
        
    End If
    
End Sub

Dave
 

rapitorres

New Member
Joined
Oct 5, 2017
Messages
34
Office Version
  1. 2010
Platform
  1. Windows
Hi Dave it works really well.
this is exactly what I need. I need to practice more :) i see you added do function
VBA Code:
ans = InputBox("Enter Quantity", , "1")
Thank you so much. appreciate this. that little tweak help me. :)
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,790
Office Version
  1. 2019
Platform
  1. Windows
You are welcome, glad we were able to help

Appreciate feedback

Dave
 

Forum statistics

Threads
1,141,049
Messages
5,703,937
Members
421,321
Latest member
blusky4

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