userform code strange anomally

[pwwato]

Hi All

Thanks for taking the time to look at this and any help given is much appreciated.

I have a userform for contacts that contains various controls and a multipage with dynamically created pages referencing a site worksheet, on the userform there is a add lift button.

What I am trying to do is depending on which multipage page (sites) is selected when add lift is pressed then another userform called liftnewform is opened and various controls on this userform are filled in so the first part

ADD LIFT code is

Public Sub ADDLIFT_Click()
Dim sp As Double
Dim spname As String


If MultiPage2.Pages.Count = 1 Then
MsgBox "NO SITES SETUP FOR:-" & CONTACT.NAME1, vbOKOnly, "York Elevator Services Ltd"
Exit Sub
Else
If MultiPage2.Pages.Count >= 1 Then
sp = CONTACT.MultiPage2.Value
spname = CONTACT.MultiPage2.Pages(sp).NAME
LIFTNEWFORM.LSREF = spname
LIFTNEWFORM.Caption = CONTACT.NAME1
LIFTNEWFORM.Show
LIFTNEWFORM.LUPDATE.Visible = False
LIFTNEWFORM.LSAVE.Visible = True

End If
End If
End Sub

This loads the form and fills in the forms caption and site ref on the new lift form

THE FORMS INITIATE code is a find command to find the contact ref then check the next columns value and if it is the same as the site ref then enter the site name from same row using column offset

Dim liftref As String
Dim slastrow As String
Dim lastrow As String
'
Dim WHAT_TO_FIND As String
Dim ws As Excel.Worksheet
Dim FoundCell As Excel.Range
Dim R As Long
Dim SMSGVAL As String
Dim SMSGR As String
Dim sp As Double
Dim spname As String
Dim SCL As Range
Dim STRF As String
Application.ScreenUpdating = False
Application.Workbooks("yesdatav2.xlsm").Sheets(5).Activate
Application.Workbooks("yesdatav2.xlsm").Sheets("lifts").Select

With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).ROW
End With


If CONTACT.CINFO.Value = 1 Then
Application.Workbooks("yesdatav2.xlsm").Sheets(4).Activate
Application.Workbooks("yesdatav2.xlsm").Sheets("sites").Select


With ActiveSheet
slastrow = .Cells(.Rows.Count, "A").End(xlUp).ROW
End With

Set ws = Sheet4
WHAT_TO_FIND = CONTACT.REF.Value
Set FoundCell = ws.Range("A:A").Find(what:=WHAT_TO_FIND, lookat:=xlWhole)
If Not FoundCell Is Nothing Then Check contact ref for match in site sheet


SMSGVAL = CONTACT.REF
sp = CONTACT.MultiPage2.Value
spname = CONTACT.MultiPage2.Pages(sp).NAME

For Each SCL In Sheet4.Range("A1:A" & slastrow) Check all matched contact ref's next cell value against the active page name which is the site ref
If SCL.Value = SMSGVAL Then
SMSGR = SCL.Offset(, 1).Text

If SMSGR = spname Then
R = SCL.Offset.ROW
LIFTNEWFORM.LROW = lastrow
LIFTNEWFORM.SNAME.Value = SCL.Offset(, 3).Value

Exit Sub
Else
End If
Else
End If
Next SCL
Else
MsgBox "NO MATCHING SITE REF FOUND"
End If
Exit Sub

The problem I am having is on first click of add lift it works perfectly whichever page is selected. once liftnewform is closed and another page(site) on multipage is selected and add lift pressed again, it works but sname retains previous value if I close form and press add lift again it works correctly and sname has right value. It does this everytime I close and select a different page unless I restart the contacts form.

it is not a massive issue but very annoying as I cant seem to pin point the cause.

Thanks for any help given

 

[Logit]

.
Can you post a copy of your workbook to a download site like: DropBox or some other cloud service ? Makes it alot easier to understand and troubleshoot. Leave out any confidential
data.