Tanyaann1995
Board Regular
- Joined
- Mar 24, 2021
- Messages
- 62
- Office Version
- 2016
- Platform
- 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:
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: