Solved

Excel Macro User Form to imput parameters

Posted on 2013-06-13
5
423 Views
Last Modified: 2013-06-25
I have a macro that works and is working fine.  This is the code.

Sub DeleteZeros()
Dim ws As Worksheet
Dim I As Long, FinalRow As Long

For Each ws In ActiveWorkbook.Worksheets
ws.Activate
    

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    For I = FinalRow To 12 Step -1
    If (Cells(I, 19).Value = "") Then GoTo lastline
        If (Cells(I, 19).Value = 0#) And (Cells(I, 23).Value = 0#) And (Cells(I, 27).Value = 0#) Then

'Rows(I).Interior.ColorIndex = 2
Rows(I).Delete Shift:=xlUp
        End If
lastline:
    Next I
    
    Next ws

End Sub

Open in new window


What it does, is searches a worksheet for zeros, and if all 3 have zeros it deletes the line, and skips null lines to preserve formatting.  And runs the process through all of the worksheets in a workbook.   this works great however the end user has a number of report to generate with this code with varying column numbers and counts.

User Form
I created this user form to handle the input.  Here is what I need to do.

work this out so the form launches, and the user can choose how many columns to search (1,2 or3), what those columns are (Ideally by column letter, and I am not sure how to convert that yet) and what and what to search for.  

I would like to set default values to the original macro.  3 columns selected, those values and to search for 0#.

And I have no idea how to make this work.  :(

I may even be doing this the wrong way, I think I need to pass the parameters from the form to the macro using variables.  I am not sure how to make it understand the column count, or how to add additional AND statements with each check box to add or remove how many columns.  

And I am not sure how to convert the column number to the column letter.

I have done things like this in Access before and it does not seem to work quite the same way and I am a little lost.

Thanks for your help.
0
Comment
Question by:larspanky
  • 4
5 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39246211
Hi,

And I am not sure how to convert the column number to the column letter.

You will probably find every "Expert" can propose a different method.

Here is just one...

To display the alphanumeric column reference for column number 12:

MsgBox Split(Columns(12).Address(ColumnAbsolute:=False), ":")(0)

BFN,

fp.
0
 

Author Comment

by:larspanky
ID: 39246427
I will give that a try.  Thank you.

Any thoughts on the column selection, or how I can call only parts of the macro with the form?

Or would it be better to write the macro into the form?
0
 

Author Comment

by:larspanky
ID: 39258150
The customer changed the scope slightly so I now only need to include one column.  

The user would input the one column they want to search, by column letter from A-ZZ and I would prefer to allow a variable for what they are searching for but the requirement is only zeros.  

I am open to any method, I can scrap the user form if its easier to use a MSGbox.  If you can help me get that done, I can close this and award points.

How would you do it?
0
 

Accepted Solution

by:
larspanky earned 0 total points
ID: 39264750
This is the code I ended up delivering.  They were happy with only selecting one column.

The change produces an input box and processes the macro based on whatever column you select.  Works Great.

Sub DeleteZeros_SingleColumn()
Dim ws As Worksheet
Dim I As Long, FinalRow As Long
Dim x As Range
'Converts selected column to integer
Dim y As Integer
On Error Resume Next
'Select Cells in Column to process
Set x = ActiveCell
'of selection select whole column
y = x.Column
'input box to select column with mouse
Set x = Application.InputBox("Select the column to Search using the mouse", Type:=8)
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
    
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    For I = FinalRow To 12 Step -1
'Skip null value rows
    If (Cells(I, y).Value = "") Then GoTo lastline
        If (Cells(I, y).Value = 0#) Then
Rows(I).Delete Shift:=xlUp
        End If
lastline:
    Next I
'Scroll to next worksheet
    Next ws

End Sub

Open in new window

0
 

Author Closing Comment

by:larspanky
ID: 39274211
I worked it out on my own.
0

Featured Post

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question