Passing variable from macro to userform

shadow74uk

New Member
Joined
Jun 4, 2007
Messages
6
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!! :)
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Is this code supposed to just validate whether a user has entered something too long in a cell?
 

shadow74uk

New Member
Joined
Jun 4, 2007
Messages
6
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-known Member
Joined
Jul 8, 2002
Messages
11,547
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

New Member
Joined
Jun 4, 2007
Messages
6

ADVERTISEMENT

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

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
I have to log off now ... maybe someone else no here can look at your code. I'll be back tomorrow sometime.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547

ADVERTISEMENT

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:
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

New Member
Joined
Jun 4, 2007
Messages
6
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,590
Messages
5,637,279
Members
416,963
Latest member
samfuge

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