input box with 2 critera

still learning · Jan 21, 2019

Hi
I’m using an input box to have the user start a macro at a certain cell.
An Icon on the Ribbon starts the macro which brings up the input box
The user enters a cell number (without the letter)
Then a macro runs 3 times and the cursor ends up at the next cell to enter data

Sometimes the macro has to run more than 3 times
I’m wondering if I can have an input box with two criteria 1) where to start macro and 2) how many times
Also, How do I have the >>>For i = 1 to 3 change to a different number using an input box.
I probably can have a second input box come up with how many times the macro runs,
Or maybe an Userform. BUT I don’t know how to change >>number of times.
1 input box would look nicer

This is what the Ribbon Icon starts

HTML:

Sub startingpointforfill()
  On Error GoTo errorline
  Startbox   ‘ this brings up the input box
  numberoffill1    ‘this is how many times the macro runs
  lastrow       ‘ this puts the cursor on next cell to inter input
  errorline: Exit Sub
  End Sub

This is the input box macro

HTML:

Sub startbox()
  n = inputbox("                 Where Should the Fill Color Start?")
  Range("B" & n).Select
  End Sub

This this macro to make >>forfillingcolor<<< 3 times

HTML:

Sub numberoffill1()
    ' this runs macro 3 times
  Dim i As Long
  For i = 1 To 3
  Call forfillingcolor
  Next i
  End Sub

mike

Michael M · Jan 21, 2019

Maybe this, but I don't see why you need to select B & n
AND
What does for filling color actually do ??

Code:

Sub startbox()
Dim i As Long
n = InputBox("Where Should the Fill Color Start?")
x = InputBox("How many times to run?")
Range("B" & n).Select
    For i = 1 To x
        Call forfillingcolor
    Next i
End Sub

still learning · Jan 21, 2019

hi Michael M

I won't be able to try it till tomorrow.

Do I still need the line>>>> numberoffill1 (the macro that makes the other run 3 times) in the first macro?

What happens is : data is entered in B thru J using as many rows as needed
after, the Ribbon Icon is used to sort the new data with the existing data base on B (the date -- A to Z)
ever other row has a fill color to make it easier to follow the data B5:J5>>no fill, B6:J6 >>fill light blue, B7:J7 >> no fill, etc
When the data is sorted, the fill doesn't go with the row.(I added more macros to over come this)
Then an input box answer tells the where to start the macro "forfillingcolor". I found I did't need the letter B, just the number.
What that does is fills B thru J will fill color light blue, drop 2 lines and do it again. IF it has to run more than 3 times, the ribbon icon is used again.
I am trying to see if there is a way to combine the number of times with where the cursor is to start.
I am still learning macros and each answer helps.
between google and this forum, I was able to write these macros

HTML:

Sub forfillingcolor1()
     ' forfillingcolor Macro
     ' put cursor on color cell
    ActiveCell.Range("A1:I1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 16645315
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    ActiveCell.Offset(2, 0).Range("A1").Select
End Sub

mike L

Its like a check book register where each other line is colored. I couln't get the fills to go with the data so I wrote macros to fix it

still learning · Jan 21, 2019

Hey Mike

I was able to try your macro
Thank you it works perfect.

I Just changed the startbox macro and left the rest alone

Thanks again

Mike L

Michael M · Jan 21, 2019

In that case use

Code:

Sub startbox()
Dim i As Long
n = InputBox("Where Should the Fill Color Start?")
x = InputBox("How many times to run?")
Range("B" & n).Select
    For i = 1 To x
    With Range("A1:I1").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 16645315
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Next i
End Sub

and delete the fillcolor macro

still learning · Jan 21, 2019

Much Cleaner

I took out " numberoffill1" in the start box macro

Thank You.
See, I'm learning new things, YEA

Now... is there a way to increase the font size of what's the question in the input box?

Mike L

input box with 2 critera

still learning

Well-known Member

Excel Facts

Michael M

Well-known Member

still learning

Well-known Member

still learning

Well-known Member

Michael M

Well-known Member

still learning

Well-known Member

Similar threads

Forum statistics

Share this page

input box with 2 critera

still learning

Well-known Member

Excel Facts

Michael M

Well-known Member

still learning

Well-known Member

still learning

Well-known Member

Michael M

Well-known Member

still learning

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock