Vba macros

saurabhlotankar

New Member
Joined
Apr 20, 2016
Messages
21
Hi all,

I have an excel file with 4 columns. Columns names are No, Work, First name and time required respectively.

I have given sample data below.

NoWorkFirst nameTime required
1Work1A_1106
2Work1A_297
3Work2A_390
4Work2A_420
5Work2A_5109
6Work2A_6104
7Work2A_734
8Work2A_837
9Work3A_989
10Work3A_1077
11Work3A_1162
12Work3A_12116

<colgroup><col span="4"></colgroup><tbody>
</tbody>



I want 3 simple macros.

First one is, I want to create a dropdown button from active controlX and assign a macro to it. Dropdown should have list of works mentioned in column B (work). And whenever I will select any value ( any work) from the dropdown it should automatically color code rows with same work name in column B.

Second is, Create Text Box from active contolX. It should Filter the list in column "D" to display all records where " Time Required" is greater than the number mentioned in the textbox object.

It would be great if you could help me with this.

Thanks.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

MrJoosten

Board Regular
Joined
Nov 12, 2016
Messages
118
Code:
Sub macro1()
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:D" & lr).AutoFilter
Range("B1:B" & lr).Copy
Range("I1:I" & lr).Select
ActiveSheet.Paste
ActiveSheet.Range("I1:I" & lr).RemoveDuplicates Columns:=1, Header:=xlNo
Cells(2, 5).Activate
With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=I2:I" & lr
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
End With

End Sub

Sub macro2()
Dim lr As Long
Dim inputValue As Long
Dim filterRng As Range
inputValue = InputBox("fill in the minimum required time", "Req time")
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set filterRng = Range("A1:D" & lr)
filterRng.AutoFilter Field:=4, Criteria1:=">" & inputValue, Operator:=xlFilterValues
End Sub

Sub macro3()
Dim lr As Long
Dim filterRng As Range

lr = Cells(Rows.Count, 1).End(xlUp).Row
Set filterRng = Range("A1:D" & lr)
filterRng.AutoFilter Field:=2, Criteria1:=Range("E2").Value, Operator:=xlFilterValues

end sub

I'm still new, but hope I could help you a little bit. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,126,992
Messages
5,622,029
Members
415,874
Latest member
JockPC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top