VBA Code not working in module but running in sheet

[Tanyaann1995]

Hi,

I have used Custom UI Editor to customize a ribbon on an excel file and I had entered the code in a module but when i click on the button on customized ribbon, the code is not working properly. The code only works when i run it from a worksheet instead of module. I don't understand how the same code runs perfectly in a sheet but it doesn't work properly from a module. Please can you explain why. Is this because there is something wrong with my code? Else, is there a way for the customized ribbon to run code from the sheet instead of module? The below is my code:

Function sheetExists(sheetToFind As String) As Boolean
    sheetExists = False
    For Each Sheet In Worksheets
        If sheetToFind = Sheet.Name Then
            sheetExists = True
            Exit Function
        End If
    Next Sheet
End Function

Sub price(control As IRibbonControl)

Dim pno As String
Dim LastRowinMainSheet As Long
Dim j As Integer
Dim i As Integer
Dim f As Workbook
Dim rgFound As Range
Dim lastrow As Long
Dim m As Integer
Dim lrow As Long
Dim pfind As Range
Dim wb As Workbook

Set wb = ActiveWorkbook

LastRowinMainSheet = wb.Worksheets(2).Range("E:E").Find(What:="*", _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row
lastrow = wb.Worksheets(2).Range("D:D").Find(What:="*", _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row
If LastRowinMainSheet > lastrow Then
m = LastRowinMainSheet
Else
m = lastrow
End If
If sheetExists("Pricebook") = False Then
wb.Sheets.Add(After:=Sheets("Emerson COMMERCIAL OFFER")).Name = "Pricebook"
Set f = Workbooks.Open("\\emrsn.org\VC-Drive_N\AEDU1_INSIDE_SALES\SPARES\Pricelist.xlsx", True, True)
f.Worksheets(1).Range("A1:U2").Copy
wb.Worksheets("Pricebook").Range("A1:U2").PasteSpecial (xlPasteAll)
For j = 24 To m
If IsEmpty(Cells(j, 7).Value) = True Then
If IsEmpty(Cells(j, 4).Value) = True Then
   pno = wb.Worksheets(2).Cells(j, 5).Value
   Set rgFound = f.Worksheets(1).Range("B:B").Find(What:=pno, LookIn:=xlValues, LookAt:=xlWhole)
   If rgFound Is Nothing Then
   wb.Worksheets(2).Cells(j, 7).Value = "P/N not found"
    Else
        wb.Worksheets(2).Cells(j, 4).Value = rgFound.Offset(0, 1).Value
        wb.Worksheets(2).Cells(j, 6).Value = rgFound.Offset(0, 2).Value
        wb.Worksheets(2).Cells(j, 7).Value = rgFound.Offset(0, 3).Value
        wb.Worksheets(2).Cells(j, 12).Value = rgFound.Offset(0, 12).Value
        wb.Worksheets(2).Cells(j, 28).Value = rgFound.Offset(0, 13).Value
        rgFound.EntireRow.Copy
        wb.Worksheets("Pricebook").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
        Application.CutCopyMode = False
    End If
Else
pno = wb.Worksheets(2).Cells(j, 4).Value
Set rgFound = f.Worksheets(1).Range("C:C").Find(What:=pno, LookIn:=xlValues, LookAt:=xlWhole)
   If rgFound Is Nothing Then
   wb.Worksheets(2).Cells(j, 7).Value = "P/N not found"
    Else
        wb.Worksheets(2).Cells(j, 5).Value = rgFound.Offset(0, -1).Value
        wb.Worksheets(2).Cells(j, 6).Value = rgFound.Offset(0, 1).Value
        wb.Worksheets(2).Cells(j, 7).Value = rgFound.Offset(0, 2).Value
        wb.Worksheets(2).Cells(j, 12).Value = rgFound.Offset(0, 11).Value
        wb.Worksheets(2).Cells(j, 28).Value = rgFound.Offset(0, 12).Value
        rgFound.EntireRow.Copy
        wb.Worksheets("Pricebook").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
        Application.CutCopyMode = False
    End If
End If
End If
Next j
Else
Set f = Workbooks.Open("\\emrsn.org\VC-Drive_N\AEDU1_INSIDE_SALES\SPARES\Pricelist.xlsx", True, True)
For j = 24 To m
If IsEmpty(Cells(j, 7).Value) = True Then
If IsEmpty(Cells(j, 4).Value) = True Then
   pno = wb.Worksheets(2).Cells(j, 5).Value
   Set rgFound = f.Worksheets(1).Range("B:B").Find(What:=pno, LookIn:=xlValues, LookAt:=xlWhole)
   If rgFound Is Nothing Then
   wb.Worksheets(2).Cells(j, 7).Value = "P/N not found"
    Else
        wb.Worksheets(2).Cells(j, 4).Value = rgFound.Offset(0, 1).Value
        wb.Worksheets(2).Cells(j, 6).Value = rgFound.Offset(0, 2).Value
        wb.Worksheets(2).Cells(j, 7).Value = rgFound.Offset(0, 3).Value
        wb.Worksheets(2).Cells(j, 12).Value = rgFound.Offset(0, 12).Value
        wb.Worksheets(2).Cells(j, 28).Value = rgFound.Offset(0, 13).Value
        rgFound.EntireRow.Copy
        wb.Worksheets("Pricebook").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
        Application.CutCopyMode = False
    End If
Else
pno = wb.Worksheets(2).Cells(j, 4).Value
Set rgFound = f.Worksheets(1).Range("C:C").Find(What:=pno, LookIn:=xlValues, LookAt:=xlWhole)
   If rgFound Is Nothing Then
   wb.Worksheets(2).Cells(j, 7).Value = "P/N not found"
    Else
        wb.Worksheets(2).Cells(j, 5).Value = rgFound.Offset(0, -1).Value
        wb.Worksheets(2).Cells(j, 6).Value = rgFound.Offset(0, 1).Value
        wb.Worksheets(2).Cells(j, 7).Value = rgFound.Offset(0, 2).Value
        wb.Worksheets(2).Cells(j, 12).Value = rgFound.Offset(0, 11).Value
        wb.Worksheets(2).Cells(j, 28).Value = rgFound.Offset(0, 12).Value
        rgFound.EntireRow.Copy
        wb.Worksheets("Pricebook").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
        Application.CutCopyMode = False
    End If
End If
End If
Next j
End If

f.Close False
Set f = Nothing
lrow = wb.Worksheets("Pricebook").Cells(wb.Worksheets("Pricebook").Rows.Count, "A").End(xlUp).Row
For m = 3 To lrow
pno = wb.Worksheets("Pricebook").Cells(m, 2).Value
Set pfind = wb.Worksheets(2).Range("E:E").Find(What:=pno, LookIn:=xlValues, LookAt:=xlWhole)
If pfind Is Nothing Then
wb.Worksheets("Pricebook").Rows(m).Interior.Color = RGB(41, 247, 110)
End If
Next m
wb.Worksheets("Pricebook").Range("A:U").AutoFilter Field:=2, Criteria1:=RGB(41, 247, 110), Operator:=xlFilterCellColor
wb.Worksheets("Pricebook").AutoFilter.Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete (xlShiftUp)
wb.Worksheets("Pricebook").ShowAllData
wb.Worksheets(2).Activate

End Sub

 

[RoryA]

the code is not working properly

Please be more specific.

 

[Tanyaann1995]

Please be more specific.

Hi,

Initially, I had entered the code in Sheet2 and the code was running perfectly. But, when I removed and added the same code in a module, the code is not working properly. Is this because there is something wrong with my code? Else, is there a way for the customized ribbon to run code from the sheet instead of module?

 

[RoryA]

Again, what does "not working properly" actually mean? What is (or isn't) happening?

 

[Tanyaann1995]

Again, what does "not working properly" actually mean? What is (or isn't) happening?

Hi,

The code is supposed to find the corresponding description and price against each item number entered in a column. The final result displays this information in Sheet2 in the workbook. When the code is pasted under Sheet2, the description and price columns are fully populated and the code works perfectly. But, when the code is pasted in the module, the description and price columns are empty even if the item numbers are valid and part of the database in which this code searches.

Another strange thing i noticed was that when i click F8 in the module, the code runs perfectly and I get all the results. But, the code isn't working when i click on Run in the module.

 

[RoryA]

If you put it in a worksheet module, then references like Cells(j, 7).Value refer to cells in that sheet. If it's in a normal module, they refer to cells on the active sheet at the time the code runs, which is probably not what you want. You need to specify the workbook/sheet that they apply to.

 

[Tanyaann1995]

If you put it in a worksheet module, then references like Cells(j, 7).Value refer to cells in that sheet. If it's in a normal module, they refer to cells on the active sheet at the time the code runs, which is probably not what you want. You need to specify the workbook/sheet that they apply to.

Thanks. This worked.