Passing variable from macro to userform

[shadow74uk]

Please accept my apologises for this question! I'm self-taught at using VBA, inbetween my normal work, and so my programming practises aren't particularly good!

I'm trying to get a userform (frmPochange) to load and show and have a label (lblWrong) populated with a variable (pocheck) that is from a procedure running (manifest_PO_check) in Module1.
I've attached the code below. I'm sure it's a pretty obvious reason and something to do with my messy, non-declaring coding but I can't work it out! Please help.

Also as the form is loaded in the middle of a procedure does it stop executing the code until the form is closed?
----------------------------------------------------------------
Public Sub manifest_PO_check()
Dim pocheck As Variant
i = 5
Do Until Cells(i, 5) = ""
Let pocheck = Cells(i, 5)
If Len(pocheck) <> 10 Then
Load frmPochange '
End If
Let Cells(i, 5) = pocheck
i = i + 1
Loop
End Sub
-------------------------------------------------------------
Public Sub CommandButton1_Click()
pocheck = frmPochange.txtCorrect.text
frmPochange.Hide
End Sub

Public Sub UserForm_Initialize()
frmPochange.lblWrong.Caption=pocheck
frmPochange.Show
End Sub
------------------------------------------
Thanks in advance

Sharif
PS. Please no 'tutting' at my poor code!Work won't send me on a course yet!!

 

[GlennUK]

Is this code supposed to just validate whether a user has entered something too long in a cell?

 

[shadow74uk]

Yes. It checks to see if the number in the cell has 10 digits. If it hasn't got 10 digits then it calls the form up to ask for the correct number to be entered.

The problem I'm having is getting the number that doesn't have 10 digits (pocheck) to be displayed in the label and then the number entered intot he textbox to be put back into the cell!

It is part of a bigger macro that works fine. This is just a small validation check. I wanted the userform so that I can get anyone running it.

Thanks
sharif

 

[GlennUK]

Well, apart from the fact that I'd have Cell Validation to prevent such entries occuring in the sheet, how about trying
frmPochange.Show
instead of Load frmPochange , and remove the .Show line of code from the Initialize routine.

 

[shadow74uk]

Thanks for that. I've tried it and get the same results. The form shows but the label is blank.

The initial data is on a spreadsheet sent into me from a supplier so I can't validate what is being entered intially unfortunately

Is it something to do with how I declare the variables and this is why the form won't recognise pocheck?

Any more help will be apprecaited. Otherwise I'll have to show people how to do a manual check. Which isn't the end of the world but I'd lvoe to knwo what I'm doing wrong!

Sharif

 

[GlennUK]

I have to log off now ... maybe someone else no here can look at your code. I'll be back tomorrow sometime.

 

[GlennUK]

Rather than trying to unpick your code and trying to make it work, I've just used the same objects in the same userform ( assuming that CommandButton2 is the Cancel button ), and written this code:

Public pocheck
Private Sub CommandButton2_Click()
Unload Me
End Sub
Public Sub CommandButton1_Click()
If Len(frmPochange.txtCorrect.Text) <> 10 Then Exit Sub
pocheck.Value = frmPochange.txtCorrect.Text
Do Until pocheck.Value = ""
    Set pocheck = pocheck.Offset(1)
    If Len(pocheck.Value) <> 10 Then
        frmPochange.lblWrong.Caption = pocheck.Value
        frmPochange.txtCorrect.Text = ""
        Exit Do
    End If
Loop
If pocheck.Value = "" Then Unload Me
    
End Sub

Public Sub UserForm_Initialize()
' find first one
blnfound = False
i = 5
Do Until Cells(i, 5) = ""
Set pocheck = Cells(i, 5)
If Len(pocheck.Value) <> 10 Then
    frmPochange.lblWrong.Caption = pocheck.Value
    frmPochange.txtCorrect.Text = ""
    blnfound = True
    Exit Do
End If
i = i + 1
Loop
If Not blnfound Then End
    
End Sub
Public Sub manifest_PO_check()
    Me.Show
End Sub

 

[shadow74uk]

Glenn,

Thank you so much for your help. I never thought of putting the code in the userform! That makes much more sense.

Thanks again
Sharif

 

[GlennUK]

Glad to help.