What Im trying to do is this:
Im making a range filter for flagging data. I want the user to be prompted as such
How many flags are there in the set?
on the next window I either want A) There to be that many sets of "upper bound: " and "lower bound: " text areas OR B) I want to spawn up to that many windows concurrently.
I've just recently started learning VB and now Im trying to get into UserForms. The code Im converting from is as followed (this is the tool without user forms)
Sub flag()
'Finds the current worksheet name
Dim Sheet_Name As String
Sheet_Name = ActiveSheet.Name
'Finds the last row of data
Dim lastRow As Integer
lastRow = ActiveWorkbook.Sheets(Sheet_Name).Cells(Rows.Count, 1).End(xlUp).Row
'Finds the last column of data
Dim lastColumn As Long
lastColumn = ActiveWorkbook.Sheets(Sheet_Name).Cells(1, Columns.Count).End(xlToLeft).Column
'findCol is User input for flag column
findCol = InputBox(Prompt:="What column would you like to flag? (Please match exact spelling).", Title:="Column Name: ")
findCol = StrConv(findCol, vbUpperCase)
'Cycles the Columns searching for findCol match
For i = 1 To lastColumn
'Evaluates the current column name and makes it capitol
ColName = Range(Cells(1, i).Address).Text
ColName = StrConv(ColName, vbUpperCase)
'Evaluates ColName vs findCol
If ColName = findCol Then
'inserts a column before found column for flagging
Columns(i).Insert
'names the flag column
Range(Cells(1, i).Address).Value = InputBox(Prompt:="Please name your flag column.", Title:="Column Name: ")
Dim filter As Integer
'Checks to see if its a numeric filter
filter = MsgBox("Is your flag a number filter?", vbYesNo, "Flag Filter")
If filter = vbYes Then 'Yes its a number filter
'checks to see how many filters are to be applied
Dim numFilt As Integer
numFilt = InputBox(Prompt:="How many flags are there?", Title:="Number of Flags: ")
For j = 1 To numFilt
'checks to see if its the last filter being applied
If j = numFilt Then
'Checks to see if last flag is for everything else
Dim allElse As Integer
allElse = MsgBox("would you like to flag everything else the same?", vbYesNo, "Flag all else?")
If allElse = vbYes Then
'finds every blank row and flags it
For h = 2 To lastRow
If Range(Cells(h, i).Address).Text = "" Then
Range(Cells(h, i).Address).Value = j
End If 'ending flagger
Next h
Exit For
End If 'ending allElse
End If 'ends last filter check
'finding upper and lowerbound
Dim filtUpper As Integer
filtUpper = InputBox(Prompt:="What is the Upper bound of filter: " & j & "?", Title:="Upper to be flagged: " & j)
Dim filtLower As Integer
filtLower = InputBox(Prompt:="What is the Lower bound of filter: " & j & "?", Title:="Lower to be flagged: " & j)
'Flags all rows that match criteria
For k = 2 To lastRow
If Range(Cells(k, i + 1).Address).Value >= filtLower And Range(Cells(k, i + 1).Address).Value <= filtUpper Then
Range(Cells(k, i).Address).Value = j
End If 'ending Criteria flagger
Next k
Next j
Else
MsgBox ("Its not a number filter") 'Placeholder
End If 'ends filter selection
Exit For 'once column has found match
End If 'ending column match
Next i
End Sub
Bookmarks