• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to define action in VBA, is it possible ?

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,

I have a data, I want to color any particular row from the data.

Coloring the row is an action, can we define this action.

The purpose is, if I have three different actions such Color, Bold, Delete.
I want to define them at the start of the code.

Can anyone help me in this please.
I have also attached a file for reference.

Please note - this is not urgent, just doing it for learnign purpose.
 

Attachments

What about using Conditional Formatting?

You haven't told us what conditions will result in what formats and your code doesn't tell me either
 
Yes Sir, you are right, we can do this by Conditional Formatting.

The reason I am trying to do it by VBA is, we have diffenent types of data.
There are multiple requirements as per the data.

Some people highlight certain text, some will delete, some will "Bold" it.
I was just thinking if could prepare a macro, which will give them option to Input the field name & choose the action, it will be good.

Till now I have written three conditions, now I want to write a code which will select the action.

Code: 
'Color Data
        Dim Ac As Long
   
        'For Each rn In MyRng
            'If rn.Value = MyStrg Then
                'rn.Select
                'Ac = ActiveCell.Row
                'Cells(Ac, 1).Select
                'ActiveCell.Resize(1, Lc).Interior.Color = 65535
                'Else
            'End If
        'Next
   
        'Bold Data
   
        'For Each rn In MyRng
            'If rn.Value = MyStrg Then
                'rn.Select
                'Ac = ActiveCell.Row
                'Cells(Ac, 1).Select
                'ActiveCell.Resize(1, Lc).Font.Bold = True
                'Else
            'End If
        'Next
   
        'Delete Data
   
        For Each rn In MyRng
            If rn.Value = MyStrg Then
                rn.Select
                Ac = ActiveCell.Row
                Cells(Ac, 1).Select
                ActiveCell.Resize(1, Lc).Select
                Selection.Delete
                Else
            End If
        Next

Thanks for the help. :)

P.S. - "You haven't told us what conditions will result in what formats and your code doesn't tell me either " I read this line now.

Sir, does my reply above is supfficient, or am I missing something. :(
 
Hi Sachin ,

Can you check your file now ?

I have made a minor change in the SelectColor procedure ; try it , and then see if the same changes can be incorporated in the other procedures.

I'd like to point out 2 significant issues :

1. When you are getting input from a user , do not use Application.ScreenUpdating = False

2. Read up on the difference between Application.InputBox and InputBox here :

http://msdn.microsoft.com/en-us/library/office/ff839468.aspx

Narayan
 

Attachments

Sir, I tried the changes as per your advice, they are working fine.

But I am facing one problem, please correct me if I am wrong.

If we use the line "Set MyStrg = Application.InputBox(prompt:="Enter field name.", Type:=8)"
then we have to select the cell which contains value. We can not write or paste in the Input Box.

I tried changing Type:=2 for text input but following line is new for me.
" If Not (Application.Intersect(rn, MyStrg) Is Nothing) Then".

The line "MyStrg = InputBox("Enter field name.")" allows user to write or paste the value in Input Box.
I think they (the end user) will want this option).

Please guide if you get time.

I am attaching both the files for your refrence.
 

Attachments

You must log in or register to reply here.
Back
Top