stumped

daulpuchich

New Member
Joined
Jun 22, 2015
Messages
36
Hello i'm new to VBA and i'm not quite sure if you can use VBA to program more than one cell or not. I've read multiple resources saying you can and that you cannot.

In this case i'm trying to create an input box where it will only accept a number that is greater than or equal to 2. Within the input box, there will be OK and CANCEL.
if the user clicks OK then the cells in column B starting at B2 would be filled in with the respective row number up to the row that was inputted by the user.
Whereas if the user clicks CANCEL then A1 will become the active cell.

I have;
Private Sub CommandButton1_Click()
Range("B10").Value = "I am here"
Dim x As Variant
' Run the Input Box.
x = InputBox("Dukes Input Box")
Msg = "Enter a number Equal or Greater than 2"
If x >= 2 Then Range("B2").Value = x

' if Cancel was pressed.
Range("A1").Activate

End Sub

This is what it would look like if the user inputted 6.
C:\Users\fipad\AppData\Local\Temp\msohtmlclip1\01\clip_image002.jpg
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

jonathann3891

Board Regular
Joined
Apr 27, 2015
Messages
109
This will do what you want with the cancel button.

Code:
Private Sub CommandButton1_Click()
Range("B10").Value = "I am here"
Dim x As Variant
' Run the Input Box.
x = InputBox("Dukes Input Box")
Msg = "Enter a number Equal or Greater than 2"
If x >= 2 Then Range("B2").Value = x


' if Cancel was pressed.
If x = vbNullString Then
Range("A1").Activate
End If


End Sub
 

daulpuchich

New Member
Joined
Jun 22, 2015
Messages
36
Thanks for the reply.

You cleaned up the pressing cancel part, thanks.

As for the actual input format, i'm trying to create an input box where if the user clicks OK then the cells in column B starting at B2 would be filled in with the respective row number up to the row that was inputted by the user.

Ex. if the user inputted 6, then:

B2 = 2
B3 = 3
B4 = 4
B5 = 5
B6 = 6

Ex. 2 if the user inputted 9, then:

B2 = 3
B3 = 3
B4 = 4
B5 = 5
B6 = 6
B7 = 7
B8 = 8
B9 = 9
This will do what you want with the cancel button.

Code:
Private Sub CommandButton1_Click()
Range("B10").Value = "I am here"
Dim x As Variant
' Run the Input Box.
x = InputBox("Dukes Input Box")
Msg = "Enter a number Equal or Greater than 2"
If x >= 2 Then Range("B2").Value = x


' if Cancel was pressed.
If x = vbNullString Then
Range("A1").Activate
End If


End Sub
 

jonathann3891

Board Regular
Joined
Apr 27, 2015
Messages
109

ADVERTISEMENT

I'm no expert by any means, but I dont think this is ideal

Ex. 2 if the user inputted 9, then:

I think that first VBA has to look at Column "A" to find the last entry and you would input your Starting number and it would count down. Other wise how would VBA know where to stop? I dont know how you could just put 9 in and it would start from there??

The code posted below will do just that. I took out "I am here", but that was for my testing purposes.

It could still use some cleaning up. For instance, I would make the input box only use numberic values. I would also Add a msgbox that alerts you if you put a value less than 1 in.
I had the msgbox working but it would show if cancel was pushed, so I took it out.


Code:
Private Sub CommandButton1_Click()Dim x As Variant


x = InputBox("Dukes Input Box:")
If x >= 2 Then
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("B2:B" & LastRow).Value = x
Range("B2:B" & LastRow) = Evaluate("B2+ROW(A1:A" & LastRow - 1 & ")-1")
End If


' if Cancel was pressed.
If x = vbNullString Then
Range("A1").Activate
End If




End Sub


I hope this helps you get on track.
 

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
Hello daulpuchich,

I this this is what you want...
Code:
Private Sub CommandButton1_Click()
        
    Dim r As Long
    Dim x As String
    
    ' Run the Input Box.
        x = InputBox("Dukes Input Box")
        Msg = "Enter a number Equal or Greater than 2"
        If Msg = "" Then
            Range("A1").Activate
            Exit Sub
        End If
        
        For r = 2 To CLng(x)
            Cells(r, "B").Value = r
        Next r

End Sub
 

daulpuchich

New Member
Joined
Jun 22, 2015
Messages
36
Thank you guys for the help!

I did manage to get a solution. For your own references you would be able to also do:

Sub RowNumber()
ans = InputBox("Enter a number Equal or Greater than 2")
If ans = "" Or Not WorksheetFunction.IsNumber(Val(ans)) Or Val(ans) < 2 Then Exit Sub
Application.ScreenUpdating = False
Range("B2:B" & Val(ans)).Value = Evaluate("ROW(B2:B" & Val(ans) & ")")
Application.ScreenUpdating = True End Sub
Hello daulpuchich,

I this this is what you want...
Code:
Private Sub CommandButton1_Click()
        
    Dim r As Long
    Dim x As String
    
    ' Run the Input Box.
        x = InputBox("Dukes Input Box")
        Msg = "Enter a number Equal or Greater than 2"
        If Msg = "" Then
            Range("A1").Activate
            Exit Sub
        End If
        
        For r = 2 To CLng(x)
            Cells(r, "B").Value = r
        Next r

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,915
Members
413,952
Latest member
JGer

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