Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel Macro User Form to imput parameters

Posted on 2013-06-13
5
Medium Priority
?
458 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

636 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