VBA Code not working in module but running in sheet

Tanyaann1995

Board Regular
Joined
Mar 24, 2021
Messages
57
Office Version
  1. 2016
Platform
  1. Windows
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:
VBA 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
 
Last edited by a moderator:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Tanyaann1995

Board Regular
Joined
Mar 24, 2021
Messages
57
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Attachments

  • Capture15.PNG
    Capture15.PNG
    9.5 KB · Views: 2

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,562
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Again, what does "not working properly" actually mean? What is (or isn't) happening?
 

Tanyaann1995

Board Regular
Joined
Mar 24, 2021
Messages
57
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,562
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 
Solution

Tanyaann1995

Board Regular
Joined
Mar 24, 2021
Messages
57
Office Version
  1. 2016
Platform
  1. Windows
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. :)
 

Forum statistics

Threads
1,144,369
Messages
5,723,950
Members
422,527
Latest member
TotalBeginner201

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