Help with Invalid Use of Null Error

gmayor · #1 12-14-2014, 11:14 PM

Frankly that won't be the only problem. While trying to decipher your code, it was apparent that you had not defined 'oRng', or 'Source' and you referred to the Userform in the userform code as e.g

Code:

UserForm1.TextBox1.Value = pStr1

rather than

Code:

Me.TextBox1.Value = pStr1

It also helps to give the userform and its elements unique names.

I am also not sure what

Code:

Me.Hide
UserForm1.Show

is trying to achieve.

The various values you have assigned to pStr1 etc are already in the list and combo boxes (assuming that part of the code works) in the hidden columns, so adding those values to the text boxes seems unnecessary? You can call them to the document directly from the list and combo boxes and make the form much simpler.

As for your original question, test whether the column has data before trying to use it and if it doesn't have a value, give it one.

Take the line

Code:

 pStr1 = .Column(0, Me.ComboBox1.ListIndex)

You could use e.g.

Code:

If Not .Column(0) = "" then
    pstr1 = .column(0)
Else
    pstr1 = " "
End if

Without the missing information the rest is guesswork.

electronicpizza · #2 12-15-2014, 01:17 AM

Quote:

Originally Posted by gmayor

As for your original question, test whether the column has data before trying to use it and if it doesn't have a value, give it one.

Take the line

Code:

 pStr1 = .Column(0, Me.ComboBox1.ListIndex)

You could use e.g.

Code:

If Not .Column(0) = "" then
    pstr1 = .column(0)
Else
    pstr1 = " "
End if

Without the missing information the rest is guesswork.

gmayor,

thanks for you help it has solved my issue, apologies about the code however I am very much a beginner learning along the way.

I have userform1 as a form for data entry into a templated letter which transfers the textbox values entered to variables and then into DoC Var fields then applying formatting.

The other userform2 is simple a box to show the listbox with the addresses and a button to import. I had to pass this as 6 separate strings as the userform1 is name , address line 1,2,3,4 and Postcode.

I realise some mistakes in the code as you pointed out I had originally used oRng to put the text right into the document but changed my mind. Purely because I wanted the user to have the visual of the address being imported otherwise it would be hidden behind the userform.

Also because I have checks on the data in the textbox not allowing most fields to be empty.

I am planning to re code with meaningful names the project started off very small but soon grew and I realised I should have done this from the start.

I have learned a lot since I started however I am always looking for tips so thank you for your time and help, if you have any feedback I'd love to hear it, I am always looking for a better way

, I have taken your points onboard.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Outlook 2013 - Strange null password issue POP3	Nibby99	Outlook	0	11-26-2014 05:16 PM
Key incorrectly shown as invalid	Confuzzed	Office	2	10-16-2014 01:33 PM
Invalid Product Key	djclinton	Office	1	09-18-2011 09:29 AM
Eliminating zeros, null values and negatives in a column	jannie	Excel	1	06-22-2011 07:03 PM
invalid recipient	impressionsIT	Outlook	1	06-22-2011 08:31 AM