Help with Invalid Use of Null Error

electronicpizza · #1 12-14-2014, 09:55 AM

I have made a userform for data entry and using an xml data source I build a dynamic table within a combobox. The issue is when the xml data source contains no data. Not everyone has 4 lines of address.

I just need help with handling a null string by converting it to something to get around the error.

The below userform is for selecting from a combobox the address of a company and inserting into the initial userform.

Code:

Option Explicit

Dim pStr1 As String
Dim pStr2 As String
Dim pStr3 As String
Dim pStr4 As String
Dim pStr5 As String
Dim pStr6 As String

Dim oRng As Word.Range
Private Sub UserForm_Initialize()
  Set oRng = Selection.Range
  'Assign UserForm control properties
  Me.cmdInsertLB.Enabled = False
  Me.cmdInsertCB.Enabled = False
  With Me.ListBox1
    .ColumnCount = 6
    '0 values in columns 3,4,5 hides these columns from view.
    .ColumnWidths = "80;80;0;0;0;"
  End With
  With Me.ComboBox1
    .ColumnCount = 5
    .ColumnWidths = "80;80;0;0;0"
    'Require valid entry from list
    .MatchRequired = True
    .MatchEntry = fmMatchEntryComplete
  End With
  'Call procedure that loads data from XML Source
  LoadData
lbl_Exit:
  Exit Sub
End Sub
Sub LoadData()
'Refer to the "Retrieving Information from an XML Document section _
    of the article at http://msdn.microsoft.com/en-us/library/aa468547.aspx
Dim xmlDoc As New MSXML2.DOMDocument30
  xmlDoc.validateOnParse = True
  xmlDoc.async = False
  xmlDoc.Load (Source)
  GetNodeValues xmlDoc.ChildNodes
lbl_Exit:
  Exit Sub
End Sub
Sub GetNodeValues(ByRef Nodes As MSXML2.IXMLDOMNodeList)
Dim xmlnode As MSXML2.IXMLDOMNode
  For Each xmlnode In Nodes
   If xmlnode.NodeType = NODE_TEXT Then
     'Load the nodeValues for named nodes into the ListBox and ComboBox
     Select Case xmlnode.ParentNode.nodeName
       Case "Name"
         With Me.ListBox1
           .AddItem
           .Column(0, Me.ListBox1.ListCount - 1) = xmlnode.NodeValue
         End With
         With Me.ComboBox1
           .AddItem
           .Column(0, Me.ComboBox1.ListCount - 1) = xmlnode.NodeValue
         End With
       Case "Address"
         With Me
           .ListBox1.Column(1, Me.ListBox1.ListCount - 1) = xmlnode.NodeValue
           .ComboBox1.Column(1, Me.ComboBox1.ListCount - 1) = xmlnode.NodeValue
         End With
                Case "Address1"
         With Me
           .ListBox1.Column(2, Me.ListBox1.ListCount - 1) = xmlnode.NodeValue
           .ComboBox1.Column(2, Me.ComboBox1.ListCount - 1) = xmlnode.NodeValue
         End With
       Case "Address2"
         With Me
           .ListBox1.Column(3, Me.ListBox1.ListCount - 1) = xmlnode.NodeValue
           .ComboBox1.Column(3, Me.ComboBox1.ListCount - 1) = xmlnode.NodeValue
         End With
       Case "Address3"
         With Me
           .ListBox1.Column(4, Me.ListBox1.ListCount - 1) = xmlnode.NodeValue
           .ComboBox1.Column(4, Me.ComboBox1.ListCount - 1) = xmlnode.NodeValue
         End With
       Case "Postcode"
         With Me
           .ListBox1.Column(5, Me.ListBox1.ListCount - 1) = xmlnode.NodeValue
           .ComboBox1.Column(5, Me.ComboBox1.ListCount - 1) = xmlnode.NodeValue
         End With
     End Select
   End If
   If xmlnode.HasChildNodes Then
     GetNodeValues xmlnode.ChildNodes
   End If
  Next xmlnode
lbl_Exit:
  Exit Sub
End Sub
Private Sub ComboBox1_Change()
  'Set state of command button
  If Me.ComboBox1.ListIndex <> -1 Then
    Me.cmdInsertCB.Enabled = True
  Else
    Me.cmdInsertCB.Enabled = False
  End If
lbl_Exit:
  Exit Sub
End Sub
Private Sub ListBox1_Click()
  'Set state of command button
  If Me.ListBox1.ListIndex <> -1 Then
    Me.cmdInsertLB.Enabled = True
  Else
    Me.cmdInsertLB.Enabled = False
  End If
lbl_Exit:
  Exit Sub
End Sub
Private Sub cmdInsertCB_Click()
  'Build data string
  With Me.ComboBox1
    pStr1 = .Column(0, Me.ComboBox1.ListIndex)
    pStr2 = .Column(1, Me.ComboBox1.ListIndex)
    pStr3 = .Column(2, Me.ComboBox1.ListIndex)
    pStr4 = .Column(3, Me.ComboBox1.ListIndex)
ERROR HERE OS PSTR5
    pStr5 = .Column(4, Me.ComboBox1.ListIndex)
    pStr6 = .Column(5, Me.ComboBox1.ListIndex)
  End With
  'Insert data string
  With oRng
   UserForm1.TextBox1.Value = pStr1
   UserForm1.TextBox2.Value = pStr2
   UserForm1.TextBox3.Value = pStr3
   UserForm1.TextBox4.Value = pStr4
   UserForm1.TextBox5.Value = pStr5
   UserForm1.TextBox6.Value = pStr6
    .Collapse wdCollapseEnd
    .Select
  End With
  Me.Hide
  UserForm1.Show
lbl_Exit:
  Exit Sub
End Sub
Private Sub cmdInsertLB_Click()
  With Me.ListBox1
    pStr1 = .Column(0, Me.ListBox1.ListIndex)
    pStr2 = .Column(1, Me.ListBox1.ListIndex)
    pStr3 = .Column(2, Me.ListBox1.ListIndex)
    pStr4 = .Column(3, Me.ListBox1.ListIndex)
ERROR HERE OS PSTR5
    pStr5 = .Column(4, Me.ListBox1.ListIndex)
    pStr6 = .Column(5, Me.ListBox1.ListIndex)
    End With
  With oRng
   UserForm1.TextBox1.Value = pStr1
   UserForm1.TextBox2.Value = pStr2
   UserForm1.TextBox3.Value = pStr3
   UserForm1.TextBox4.Value = pStr4
   UserForm1.TextBox5.Value = pStr5
   UserForm1.TextBox6.Value = pStr6
   .Collapse wdCollapseEnd
   .Select
  End With
  Me.Hide
lbl_Exit:
  Exit Sub
End Sub

gmayor · #2 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 · #3 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.

gmayor · #4 12-15-2014, 02:15 AM

Rather than use a second userform to display the data, put a label on the main form and set the label caption to have the combined address text e.g. as follows. which will give you a visual indication of what is to be inserted. You can also insert strAddress into the document.

Code:

Dim strAddress as String
With Me.ListBox1
    strAddress = .column(0) & vbcr & .Column(1) 'etc.
End With
Me.LabelName.Caption = strAddress

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