C
Centurius
I am currently working on a spreadsheet for my workplace that makes use
of the AutoFilter feature to provide reports on our inventory prices
etc. using criteria entered via a UserForm
Some of the items in the inventory list have comments attached and i
was wondering if it is possible to bring these comments with the cell
when it is transferred to my report page
my module is below, excuse the severely bloated code, i'm not too
efficient with VBA as yet but it works. (apart from the comments of
course)
TIA!
Dan
------------------------------------------------------------------------------
Private Sub Button_Create_Report_Click()
Application.ScreenUpdating = False
If TextBox_Product_Name.Value <> "" Or False Then
Sheets("Inventory_Report").Range("Searched_Name_Result").Value
= "*" & TextBox_Product_Name.Value & "*"
End If
If ComboBox_Category.Value <> "" Or False Then
Sheets("Inventory_Report").Range("Searched_Category_Result").Value =
"*" & ComboBox_Category.Value & "*"
End If
If ComboBox_SubCategory.Value <> "" Or False Then
Sheets("Inventory_Report").Range("Searched_SubCategory_Result").Value =
"*" & ComboBox_SubCategory.Value & "*"
End If
If ComboBox_Supplier.Value <> "" Or False Then
Sheets("Inventory_Report").Range("Searched_Supplier_Result").Value =
"*" & ComboBox_Supplier.Value & "*"
End If
If TextBox_Product_Code.Value <> "" Or False Then
Sheets("Inventory_Report").Range("Searched_Product_Code_Result").Value
= "*" & TextBox_Product_Code.Value & "*"
End If
If TextBox_Price_Ex_GST.Value <> "" Or False Then
Sheets("Inventory_Report").Range("Searched_Price_ExGST_Result").Value =
TextBox_Price_Ex_GST.Value
End If
If ComboBox_UOM.Value <> "" Or False Then
Sheets("Inventory_Report").Range("Searched_UOM_Result").Value =
"*" & ComboBox_UOM.Value & "*"
End If
ActiveWorkbook.Sheets("Inventory_Report").Activate
Unload Form_Create_Report
Set DataRange = Sheets("Inventory").Range("A1:G" &
Sheets("Inventory").Range("G65536").End(xlUp).Row)
DataRange.AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range("Searched_Name:Searched_UOM_Result"),
CopyToRange:=Sheets("Inventory_Report").Range("A2:G2"), Unique:=False
ActiveWorkbook.Sheets("Inventory_Report").Activate
Range("Searched_Name_Result").ClearContents
Range("Searched_Category_Result").ClearContents
Range("Searched_SubCategory_Result").ClearContents
Range("Searched_Supplier_Result").ClearContents
Range("Searched_Product_Code_Result").ClearContents
Range("Searched_Price_ExGST_Result").ClearContents
Range("G1").Formula = "FALSE"
Range("A1").Select
Application.ScreenUpdating = True
End Sub
of the AutoFilter feature to provide reports on our inventory prices
etc. using criteria entered via a UserForm
Some of the items in the inventory list have comments attached and i
was wondering if it is possible to bring these comments with the cell
when it is transferred to my report page
my module is below, excuse the severely bloated code, i'm not too
efficient with VBA as yet but it works. (apart from the comments of
course)
TIA!
Dan
------------------------------------------------------------------------------
Private Sub Button_Create_Report_Click()
Application.ScreenUpdating = False
If TextBox_Product_Name.Value <> "" Or False Then
Sheets("Inventory_Report").Range("Searched_Name_Result").Value
= "*" & TextBox_Product_Name.Value & "*"
End If
If ComboBox_Category.Value <> "" Or False Then
Sheets("Inventory_Report").Range("Searched_Category_Result").Value =
"*" & ComboBox_Category.Value & "*"
End If
If ComboBox_SubCategory.Value <> "" Or False Then
Sheets("Inventory_Report").Range("Searched_SubCategory_Result").Value =
"*" & ComboBox_SubCategory.Value & "*"
End If
If ComboBox_Supplier.Value <> "" Or False Then
Sheets("Inventory_Report").Range("Searched_Supplier_Result").Value =
"*" & ComboBox_Supplier.Value & "*"
End If
If TextBox_Product_Code.Value <> "" Or False Then
Sheets("Inventory_Report").Range("Searched_Product_Code_Result").Value
= "*" & TextBox_Product_Code.Value & "*"
End If
If TextBox_Price_Ex_GST.Value <> "" Or False Then
Sheets("Inventory_Report").Range("Searched_Price_ExGST_Result").Value =
TextBox_Price_Ex_GST.Value
End If
If ComboBox_UOM.Value <> "" Or False Then
Sheets("Inventory_Report").Range("Searched_UOM_Result").Value =
"*" & ComboBox_UOM.Value & "*"
End If
ActiveWorkbook.Sheets("Inventory_Report").Activate
Unload Form_Create_Report
Set DataRange = Sheets("Inventory").Range("A1:G" &
Sheets("Inventory").Range("G65536").End(xlUp).Row)
DataRange.AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range("Searched_Name:Searched_UOM_Result"),
CopyToRange:=Sheets("Inventory_Report").Range("A2:G2"), Unique:=False
ActiveWorkbook.Sheets("Inventory_Report").Activate
Range("Searched_Name_Result").ClearContents
Range("Searched_Category_Result").ClearContents
Range("Searched_SubCategory_Result").ClearContents
Range("Searched_Supplier_Result").ClearContents
Range("Searched_Product_Code_Result").ClearContents
Range("Searched_Price_ExGST_Result").ClearContents
Range("G1").Formula = "FALSE"
Range("A1").Select
Application.ScreenUpdating = True
End Sub