Hi all,
This is my first VBA Projet and whilst it works (in it's current stat
/ content), I've stumbled across some problems.
So if you are willing to amble through my ramblings and assist, I'll b
greatfully apreciative of any suggestion / solutions you are willing t
offer.

The Named Array's: 'Doctors' / 'Doctors Array' / 'Doctors Table' al
have data in them currently. And when using the 'RemoveButton' wil
successfully remove all records. But when I attempt to add a recor
fails due to the 'Doctors' Named Array being a #REF error due to th
last record being removed (so I'd concluded).

I attempted to add a dummy record and retried to add a record, but i
again fell over as (-and I'm assuming here again-) due to not enoug
records for the script to work.

My questions a a
- Do I need to re-write what I've completed thus far ?
- Need to build a user function to handle this problem?
- Have I overlooked some basic fundamentals in the design of th
script?
- Is my existing code well structured??
- Areas of improvement ??
- Need to see a shrink?aAgain any help is apreciated.
-If anyone would like a copy of the XLS to view, let me know.-

Cheers,
Cameron
-Brisbane, Australia-

------------------------------------------------
Code in Worksheet "*Extra Tables*"...

Code
-------------------
Private Sub AddButton_Click()
'ADD DOCTOR
DisableButtons
frmGetDetails.Show
ShowButtons
End Sub

Private Sub DeleteButton_Click()
'REMOVE DOCTOR !!
DisableButtons
frmDltDetails.Show
ShowButtons
End Sub

Public Sub DisableButtons()
CommandButton1.Enabled = False
CommandButton3.Enabled = False
ThisWorkbook.Worksheets("Extra Tables").Range("A1").Select
End Sub

Public Sub ShowButtons()
CommandButton1.Enabled = True
CommandButton3.Enabled = True
ThisWorkbook.Worksheets("Extra Tables").Range("A1").Select
End Su
-------------------
Code in Userform *frmGetDetails*...

Code
-------------------
Sub ClearText()
'Sub to clear values of all TextBox's
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
End Sub

Private Sub CancelButton_Click()
'Hide this form & unload
Me.Hide
Unload Me
End Sub

Private Sub ContinueButton_Click()

Select Case Len(TextBox1.Text)
Case 0
MsgBox "Information Required - Doctors Name." & vbCrLf & _
"This field cannot remain empty.", vbInformation, "Missing Data!"
ContinueButton.Enabled = False
TextBox1.SetFocus
'Drop out of this Sub
Exit Sub
End Select

Select Case Len(TextBox2.Text)
Case 0
MsgBox "Information Required - Brief Detail." & vbCrLf & _
"This field cannot remain empty.", vbInformation, "Missing Data!"
TextBox2.SetFocus
'Drop out of this Sub
Exit Sub
End Select

Select Case Len(TextBox3.Text)
Case 0
MsgBox "Information Required - Address Line 1 Details." & vbCrLf & _
"This field cannot remain empty.", vbInformation, "Missing Data!"
TextBox3.SetFocus
'Drop out of this Sub
Exit Sub
Case Else
Select Case Len(TextBox4.Text)
Case 0
Resp1 = MsgBox("Information Required - Address Line 2 Details." & vbCrLf & _
"Are you sure this line is to be empty?", vbYesNo, "Missing Data!")
If Resp1 = vbNo Then
TextBox4.SetFocus
'Drop out of this Sub
Exit Sub
End If
Case Else
Select Case Len(TextBox5.Text)
Case 0
Resp1 = MsgBox("Information Required - Address Line 3 Details." & _
vbCrLf & "Are you sure this line is to be empty?", vbYesNo, "Missing Data!")
If Resp1 = vbNo Then
TextBox5.SetFocus
'Drop out of this Sub
Exit Sub
End If
End Select
End Select
End Select

'Prompt User with a Message Dialog Box to confirm details.
Resp2 = MsgBox("Please confirn the following details:" & vbTab & vbCrLf & _
"Doctors Name:" & vbTab & TextBox1.Text & "." & vbTab & vbCrLf & _
"Brief Detail:" & vbTab & TextBox2.Text & "." & vbTab & vbCrLf & _
"Address Details:" & vbCrLf & _
vbTab & vbTab & TextBox3.Text & vbCrLf & _
vbTab & vbTab & TextBox4.Text & vbCrLf & _
vbTab & vbTab & TextBox5.Text & vbCrLf & _
vbTab & vbTab & TextBox6.Text, vbYesNo, "Confirm Details Entered.")

If Resp2 = vbYes Then
' Perform AddData Sub
AddData
End If
' Hide this form & unload
Me.Hide
Unload Me

End Sub

Sub AddData()

Dim strVal As String

' Work out what is the last row number under the `Doctors` Named Array.
LastRow = ThisWorkbook.Worksheets("Extra Tables").Range("Doctors").End(xlDown).Row + 1
' Go to last row and insert an entire row.
ThisWorkbook.Worksheets("Extra Tables").Range("F" & LastRow).Select
Selection.EntireRow.Insert
' Insert New Doctor Details
ThisWorkbook.Worksheets("Extra Tables").Range("F" & LastRow).Select
ActiveCell.Value = TextBox1.Text
ThisWorkbook.Worksheets("Extra Tables").Range("G" & LastRow).Select
ActiveCell.Value = TextBox2.Text
ThisWorkbook.Worksheets("Extra Tables").Range("H" & LastRow).Select
' TextBox3.Text (Address Line 1) has to have some value before getting to this point,
' so include it into strVal now.
strVal = TextBox3.Text
' Add remaining Address Lines are required.
If TextBox4.Text < "" Then _
strVal = strVal & vbLf & TextBox4.Text
If TextBox5.Text < "" Then _
strVal = strVal & vbLf & TextBox5.Text
If TextBox6.Text < "" Then _
strVal = strVal & vbLf & TextBox6.Text
'Post Address Lines to ActiveCell
ActiveCell.Value = strVal

' SORT `DoctorsTable` Array
ThisWorkbook.Worksheets("Extra Tables").Range("F15:H" & LastRow).Sort _
Key1:=Range("F15"), Order1:=xlAscending, Key2:=Range("G15"), _
Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
' Rebuild Named Ranges that have been expanded.
ActiveWorkbook.Names.Add Name:="Doctors", _
RefersTo:="='Extra Tables'!$F$15:$F$" & LastRow
ActiveWorkbook.Names.Add Name:="DoctorsArray", _
RefersTo:="='Extra Tables'!$F$15:$G$" & LastRow
ActiveWorkbook.Names.Add Name:="DoctorsTable", _
RefersTo:="='Extra Tables'!$F$15:$H$" & LastRow
End Sub

Private Sub TextBox1_Change()
' Ensure Doctors Name
If Len(TextBox1.Text) = 0 And IsNumeric(VBA.Left(TextBox1.Text, 1)) = False Then
If Len(TextBox2.Text) 0 Then
If Len(TextBox3.Text) 0 Then
ContinueButton.Enabled = True
Else
ContinueButton.Enabled = False
End If
Else
ContinueButton.Enabled = False
End If
Else
MsgBox "Docors Names generally don't start with numbers.", _
vbOKOnly, "Incorrect Details !!"
TextBox1.Text = ""
TextBox1.SetFocus
ContinueButton.Enabled = False
End If
End Sub

Private Sub TextBox2_Change()
If Len(TextBox1.Text) = 0 And IsNumeric(VBA.Left(TextBox1.Text, 1)) = False Then
If Len(TextBox2.Text) 0 Then
If Len(TextBox3.Text) 0 Then
ContinueButton.Enabled = True
Else
ContinueButton.Enabled = False
End If
Else
ContinueButton.Enabled = False
End If
Else
ContinueButton.Enabled = False
End If
End Sub

Private Sub TextBox3_Change()

If Len(TextBox1.Text) = 0 And IsNumeric(VBA.Left(TextBox1.Text, 1)) = False Then
If Len(TextBox2.Text) 0 Then
If Len(TextBox3.Text) 0 Then
ContinueButton.Enabled = True
Else
ContinueButton.Enabled = False
End If
Else
ContinueButton.Enabled = False
End If
Else
ContinueButton.Enabled = False
End If
End Sub

Private Sub Userform_Activate()
ClearText
TextBox1.SetFocus
ContinueButton.Enabled = False
End Sub
--------------------



---
Message posted from http://www.ExcelForum.com/