Upon opening worksheet check value is present in userform

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,838
Hi,

I already have a code using explicit.

Here it is below.

How / what should i now do,please advise thanks.

Code:
Option ExplicitPrivate Sub PostageCustomerDuplicateSearch_Click()


    Dim Cell As Variant
    Dim source As Range
    Dim dups As Long


    Set source = Range("B8:B5000")


    For Each Cell In source
        If Application.WorksheetFunction.CountIf(source, Cell) > 1 Then
            Cell.Interior.Color = RGB(0, 255, 0)
            dups = dups + 1
        End If
    Next Cell
    
    If dups > 0 Then
        MsgBox " DUPLICATES WERE FOUND AND HAVE BEEN HIGHLIGHTED ", vbExclamation, "POSTAGE DUPLICATE CHECKER"
    Else
        MsgBox " NO DUPLICATES WERE FOUND ", vbExclamation, "POSTAGE DUPLICATE CHECKER"
    End If


End Sub
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
2,924
.
This :

Code:
[COLOR=#333333]Option ExplicitPrivate Sub PostageCustomerDuplicateSearch_Click()[/COLOR]
Should be :

Code:
[COLOR=#333333]Option Explicit

Private Sub PostageCustomerDuplicateSearch_Click()

'rest of macro code.
[/COLOR]
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,838
Hi,
It is but it just showed up as that due to it being pasted into the post
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
2,924
.
Try without the OPTION EXPLICIT.

The macro works as presented .. here.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,838
I’ve ran out of time now and away working for one week.

I was hoping to get this fixed before I went it that was free case.

I will check upon my return

I don’t have excel on my laptop but so you know if something that will allow me to continue with this whilst away ?
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,801
Your workbook has just been opened.
PostageTransferSheet is unloaded.
How would there be anything (except the default value) in any of the userform's controls?
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,838
Morning,
I am now back & wishing to continue with this post.
Below is the code in use.

My aim is to only show userform if there are more than 1 name in the NameForDateEntryBox field

So i open my worksheet.
I see the msgbox asking if i would like to open the userform.
Selecting No would close the msgbox
Selecting Yes would then ONLY open the userform if there are more than 1 name in it.
If there is 1 name then show msgbox "No names Left"
Clicking Ok would then close msgbox

I see no other way to fix this so this will be the last attempt.

Code:
Private Sub Worksheet_Activate()
Application.GoTo Sheets("POSTAGE").Range("A" & Rows.Count).End(xlUp).Offset(1, 0), True
ActiveWindow.SmallScroll UP:=14


Dim answer As Integer
 
answer = MsgBox("Open Postage User Form ?", vbYesNo + vbQuestion, "POSTAGE USER FORM MESSAGE")
If answer = vbYes Then
PostageTransferSheet.Show
Else
  Exit Sub
End If
End Sub
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,801
The logic of your desired action is flawed.
So i open my worksheet.
I see the msgbox asking if i would like to open the userform.
Selecting No would close the msgbox
Selecting Yes would then ONLY open the userform if there are more than 1 name in it.
At that point, the only entry in the userform would be the default entries. "if there are more than 1 name" would never change.
 

Forum statistics

Threads
1,082,275
Messages
5,364,178
Members
400,785
Latest member
Mahar92

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top