if statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Phil - 31 Dec 2004 03:55 GMT

using the code below I'm repopulation my from data stored in a text file my
problem is when the text file isn't available and I try load it an error
occurs I think I need an" if docname exists" statement but my attempts at
this has failed if anyone can help me with this it would be appreciated
thanks Phil
Dim oControl As Control, DocName As String
DocName = Left(ActiveDocument.FullName, InStr(ActiveDocument.FullName,
".") - 1)
For Each oControl In Me.Controls
If oControl.Tag = "save" Then
If TypeOf oControl Is MSForms.TextBox Then
oControl.Text = System.PrivateProfileString(DocName & ".Txt",
"FormData", oControl.Name)
ElseIf TypeOf oControl Is MSForms.ComboBox Then
oControl.Text = System.PrivateProfileString(DocName & ".Txt",
"FormData", oControl.Name)
ElseIf TypeOf oControl Is MSForms.CheckBox Then
oControl.Value = CStr(System.PrivateProfileString(DocName & ".Txt",
"FormData", oControl.Name))
ElseIf TypeOf oControl Is MSForms.ListBox Then
oControl.Text = System.PrivateProfileString(DocName & ".Txt",
"FormData", oControl.Name)
End If
End If
Next oControl
End Sub

Reply to this Message

Jay Freedman - 31 Dec 2004 15:14 GMT

Hi Phil,

Try it this way...

Dim oControl As Control, DocName As String
Dim ProfileName As String
DocName = Left(ActiveDocument.FullName, InStr(ActiveDocument.FullName,
".") - 1)
ProfileName = DocName & ".txt"
If Dir(ProfileName) = "" Then
MsgBox "Could not find " & ProfileName
Exit Sub
End If

Then in each of the PrivateProfileString calls, replace the expression
DocName & ".Txt" with ProfileName.

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org

>using the code below I'm repopulation my from data stored in a text file my
>problem is when the text file isn't available and I try load it an error
[quoted text clipped - 22 lines]
>Next oControl
>End Sub

Reply to this Message

Phil - 01 Jan 2005 13:37 GMT

Cheers works a treat
thanks phil

> Hi Phil,
>
[quoted text clipped - 44 lines]
> >Next oControl
> >End Sub

Reply to this Message

Phil - 02 Jan 2005 06:42 GMT

hi jay
have a problem with the code I changed
when I open a new document and open the userform for the first time all the
checkboxes are checked but only in a ghosted when I try and save the form an
error occurs invalid use of null and highlights this line of code
System.PrivateProfileString(ProfileName, "FormData", oControl.Name) =
CStr(oControl.Value)

> Cheers works a treat
> thanks phil
[quoted text clipped - 48 lines]
> > >Next oControl
> > >End Sub

Reply to this Message

Jay Freedman - 02 Jan 2005 15:08 GMT

Hi Phil,

That isn't really enough information to diagnose what's going on.
There are a few clues, though. When you open the userform for the
first time, does the text file (ProfileName) not exist? If so, how are
you handling the initialization of the checkboxes (or aren't you doing
anything)?

It would help if you post the complete code of the Userform_Initialize
and/or Userform_Activate procedures, whichever you're using. Also, in
the form view, select a checkbox and look at the Properties pane --
what are the values of the Value and TripleState properties?

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org

>hi jay
>have a problem with the code I changed
[quoted text clipped - 58 lines]
>> > >Next oControl
>> > >End Sub

Reply to this Message

Phil - 02 Jan 2005 22:55 GMT

below is the userform intialize code
value is false
TripleState false
thanks phil

Private Sub UserForm_Initialize()
Dim oControl As Control, DocName As String
Dim ProfileName As String
DocName = Left(ActiveDocument.FullName, InStr(ActiveDocument.FullName,
".") - 1)
ProfileName = DocName & ".txt"
For Each oControl In Me.Controls
If oControl.Tag = "save" Then
If TypeOf oControl Is MSForms.TextBox Then
oControl.Text = System.PrivateProfileString(ProfileName, "FormData",
oControl.Name)
ElseIf TypeOf oControl Is MSForms.ComboBox Then
oControl.Text = System.PrivateProfileString(ProfileName, "FormData",
oControl.Name)
ElseIf TypeOf oControl Is MSForms.CheckBox Then
oControl.Value = CStr(System.PrivateProfileString(ProfileName,
"FormData", oControl.Name))
ElseIf TypeOf oControl Is MSForms.ListBox Then
oControl.Text = System.PrivateProfileString(ProfileName, "FormData",
oControl.Name)
End If
End If
Next oControl
If Dir(ProfileName) = "" Then
Exit Sub
End If

'combobox the builder the propritor
FillCombo cbpre_surveyor
FillCombo cbsit_suitable
FillCombo cbsit_removal
FillCombo cbsit_any
FillCombo cbsit_adjustment
FillCombo cbsit_removal_of
FillCombo cbcon_excavation_removal
FillCombo cbmas_retaining
FillCombo cbplu_water
FillCombo cbplu_roof
FillCombo cbplu_site
'combobox 350,400,450,500,550,600
FillCombo1 cbcon_external_depth
FillCombo1 cbcon_internal_depth
FillCombo1 cbcon_verandah_depth

Dim MyArray As Variant
MyArray = Array("Item 1", "Item 2", "Item 3", "Item 4")
With cbpre_site_design
.AddItem "N1 (28m/s)"
.AddItem "N2 (33m/s)"
.AddItem "N3 (41m/s)"
.AddItem "N4 (50m/s)"
.ListIndex = 0
End With

mpgDATA_INPUT.Pages(0).ScrollBars = fmScrollBarsVertical
mpgDATA_INPUT.Pages(0).KeepScrollBarsVisible = fmScrollBarsNone
mpgDATA_INPUT.Pages(0).ScrollHeight = 2 * mpgDATA_INPUT.Height

' Sync multipage to initial checkbox states
chkDEMOLITION_Click
chkSITE_WORKS_Click
chkTERMITE_CONTROL_Click
chkCONCRETE_Click
chkCLADDING_Click
chkCLEANUP_Click
chkDEMOLITION_Click
chkINSULATION_SARKING_Click
chkMASONRY_Click
chkPAINTING_Click
chkPAVING_Click
chkPLUMBING_Click
chkROOFING_Click
chkTIMBER_STEEL_FRAMING_Click
chkTROWELLED_COATINGS_Click
chkLANDSCAPING_Click
chkpre_footing_construction_Click
chkpre_engineering_drawing_Click
chkpre_surveyor_certificate_Click
chksit_details_of_any_adjustment_Click
chksit_special_Click
chksit_details_of_site_Click
chkcon_no_engineers_Click
chkcon_details_of_Click
chkcon_other_work_Click
chkroo_metal_Click
chkroo_tiled_Click
chkroo_box_gutters_Click
chkroo_gutters_Click
chkmas_quoins_Click
chkmas_retaining_Click
chkmas_retaining_walls_Click
chkmas_internal_Click
chkmas_internal_face_Click
chkmas_fireplace_Click
chkroo_metal_Click
chkroo_tiled_Click
chtim_floor_Click
chtim_external_Click
chtim_external2_Click
chtim_external3_Click
chtim_timber_framing_Click
chtim_steel_framing_Click
chkcla_wall_Click
chkcla_gable_Click
chkcla_eaves_Click
chkcla_verge_Click
chkmas_clay_Click
chkmas_concrete_Click
chkmas_block_Click
chkmas_tilt_Click
chkmas_ac_Click

Dim sourcedoc As Document, i As Long, myitem As Range
Set sourcedoc = Documents.Open("c:\schedule\Supplier\Roofing.doc")
For i = 2 To sourcedoc.Tables(1).Rows.Count
Set myitem = sourcedoc.Tables(1).Cell(i, 1).Range
myitem.End = myitem.End - 1
cbroo_main.AddItem myitem.Text
cbroo_gutter_manufacture.AddItem myitem.Text
Set myitem = sourcedoc.Tables(2).Cell(i, 1).Range
myitem.End = myitem.End - 1
cbroo_tiles_main.AddItem myitem.Text
Next i
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges

For i = 1 To 0
Me.cbpre_engineering_referance.AddItem i
Me.cbpre_engineering_prepared.AddItem i
Me.cbpre_engineering_date.AddItem i
Next i

End Sub

> Hi Phil,
>
[quoted text clipped - 76 lines]
> >> > >Next oControl
> >> > >End Sub

Reply to this Message

Jay Freedman - 03 Jan 2005 00:56 GMT

Hi Phil,

Here's the problem: If the text file named by ProfileString doesn't
exist, then the PrivateProfileString function passes back an empty
string. This doesn't make any problems for the textbox, combobox, or
listbox controls, since they can accept empty strings as values. For a
checkbox, though, it's a problem.

The oControl.Value property for a checkbox is a Boolean. When you pass
a string into it, VBA will do an implicit type conversion -- but it
can't handle an empty string. You might think it would be interpreted
as False, but it's actually converted to Null (or something like it).
That causes the grayed/checked box, and also causes the problem when
you try to save that value later.

To fix this, you need special handling in the checkbox case if the
profile string is empty, as follows. Also notice that you can remove
the 'If Dir(ProfileName) = "" Then...End If' part, since it now
doesn't matter whether the file exists or not.

Dim oControl As Control, DocName As String
Dim ProfileName As String, temp As String
DocName = Left(ActiveDocument.FullName, _
InStr(ActiveDocument.FullName, ".") - 1)
ProfileName = DocName & ".txt"
For Each oControl In Me.Controls
If oControl.Tag = "save" Then
If TypeOf oControl Is MSForms.TextBox Then
oControl.Text = System.PrivateProfileString(ProfileName, _
"FormData", oControl.Name)
ElseIf TypeOf oControl Is MSForms.ComboBox Then
oControl.Text = System.PrivateProfileString(ProfileName, _
"FormData", oControl.Name)
ElseIf TypeOf oControl Is MSForms.CheckBox Then
temp = System.PrivateProfileString(ProfileName, _
"FormData", oControl.Name)
If temp = "" Then
oControl.Value = False
Else
oControl.Value = CBool(temp)
End If
ElseIf TypeOf oControl Is MSForms.ListBox Then
oControl.Text = System.PrivateProfileString(ProfileName, _
"FormData", oControl.Name)
End If
End If
Next oControl

I'll also suggest that you simplify the code a bit by combining the
three other cases into one by using the Or operator, like this:

If oControl.Tag = "save" Then
If (TypeOf oControl Is MSForms.TextBox) Or _
(TypeOf oControl Is MSForms.ComboBox) Or _
(TypeOf oControl Is MSForms.ListBox) Then
oControl.Text = System.PrivateProfileString(ProfileName, _
"FormData", oControl.Name)
ElseIf (TypeOf oControl Is MSForms.CheckBox) Then
temp = System.PrivateProfileString(ProfileName, _
"FormData", oControl.Name)
If temp = "" Then
oControl.Value = False
Else
oControl.Value = CBool(temp)
End If
End If
End If

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org

>below is the userform intialize code
>value is false
[quoted text clipped - 220 lines]
>> >> > >Next oControl
>> >> > >End Sub

Reply to this Message

Phil - 03 Jan 2005 03:38 GMT

thanks jay all works great
phil

> Hi Phil,
>
[quoted text clipped - 293 lines]
> >> >> > >Next oControl
> >> >> > >End Sub

Reply to this Message

Rate this thread: