Code efficiency & Adding a row.

[Cameron]

Hi all and sundry,
Part `A` - Have the following code on a UserForm, need to know how I can
make this more efficient (if required).
----------------------------------
Private Sub CancelButton_Click()
Me.Hide
Sheet1.ShowButtons
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!"
TextBox1.SetFocus
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
Exit Sub
End Select

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

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 & ".", vbYesNo, "Confirm Details
Entered.")

Select Case Resp2
Case vbYes
MsgBox "ADD the Doctor here & now."
Case vbNo
MsgBox "Will not Proceede with ADD"
End Select

Me.Hide

End Sub
----------------------------------
Private Sub UserForm_Initialize()
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
End Sub
----------------------------------
Part `B` - At the point where it says "ADD the Doctor here & now.", I intend
to add the details provided and enter them onto the spreadsheet.
The area where I wold like to insert this data is in a Named region.

DoctorsTable = 'Extra Tables'!$C$13:$E$20

and other Named regions within this are

Doctors = 'Extra Tables'!$C$13:$C$20
DoctorsArray = 'Extra Tables'!$C$13:$D$20

The question is what code do I need to insert a row into the DoctorsTable
and sort the region.

Many thanks for any assistance.

Cheers,
Cameron

 

[Tom Ogilvy]

Turn on the macro recorder and insert the row manually, then sort the table.

This will give you the basic code you need.

 

[BrianB]

1. IMHO your code is OK. When error trapping it is often a long codin
job. Don't woory how many lines you use here, each one takesonly
microsecond to work.

2. Example code to adapt
'--------------------------------------------
Sub AddData()
Dim ExtraTables As Worksheet
Dim DoctorsTable As Range
Dim NextAddRow As Long
'-----------------------
Set ExtraTables = ThisWorkbook.Worksheets("Extra Tables")
Set DoctorsTable = ExtraTables.Range("DoctorsTable")
'- row refers to table not sheet
'- = 'Extra Tables'!$C$13:$E$20
NextAddRow = DoctorsTable.Range("A1").End(xlDown).Row - 11
DoctorsTable.Cells(NextAddRow, 1).Value = "Something"
DoctorsTable.Sort Key1:=Range("C13"), Order1:=xlAscending
Header:=xlNo, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
End Sub
'--------------------------------------------

 

[Cameron]

Hi Brian,
Thanks for the suggestion - I'm in the process of trying to implement your
suggestion and learn what I'm doing at the same time.

Another question I have is:
If I insert a row into a Named region/range, can I amend that Named
region/range with code??
For instance; the Name region/range (DoctorsTable = 'Extra
Tables'!$C$13:$E$20).
Is it possible after row insertion to amend this via code to show
$C$13:$E$21 ????

Cheers,
Cameron.

 

[Cameron]

Hi Brian,

Thanks for the suggestion - I'm in the process of trying to implement your
suggestion and learn what I'm doing at the same time.

Another question I have is:
If I insert a row into a Named region/range, can I amend that Named
region/range with code??
For instance; the Name region/range ...
(DoctorsTable = 'Extra Tables'!$C$13:$E$20).
Is it possible after row insertion to amend this via code to show
$C$13:$E$21 ????

Cheers,
Cameron.

 

[Mike Fogleman]

The beauty of a named range is that Excel always knows where it is and how
big it is. The named range will automatically include the inserted cells and
resize itself. Go to a named range after a row has been inserted/deleted and
see what the new reference is.
Mike.

 

[Cameron]

Thanks for the info Mike.
I've ended up adding the following code:

Sub AddData()
LastRow = ThisWorkbook.Worksheets("Extra
Tables").Range("C14").End(xlDown).Row + 1
ThisWorkbook.Worksheets("Extra Tables").Range("C" & LastRow).Select
Selection.EntireRow.Insert
ThisWorkbook.Worksheets("Extra Tables").Range("C" & LastRow).Select
ActiveCell.Value = TextBox1.Text
ThisWorkbook.Worksheets("Extra Tables").Range("D" & LastRow).Select
ActiveCell.Value = TextBox2.Text
ThisWorkbook.Worksheets("Extra Tables").Range("E" & LastRow).Select
ActiveCell.Value = TextBox3.Text & vbLf & _
TextBox4.Text & vbLf & _
TextBox5.Text
ThisWorkbook.Worksheets("Extra Tables").Range("C13:E" & LastRow).Sort _
Key1:=Range("C14"), Order1:=xlAscending, Key2:=Range("D14"), _
Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
ActiveWorkbook.Names.Add Name:="Doctors", _
RefersTo:="='Extra Tables'!$C$14:$C$" & LastRow
ActiveWorkbook.Names.Add Name:="DoctorsArray", _
RefersTo:="='Extra Tables'!$C$14:$D$" & LastRow
ActiveWorkbook.Names.Add Name:="DoctorsTable", _
RefersTo:="='Extra Tables'!$C$14:$E$" & LastRow
End Sub

It works which is really amazing.
Not sure if I could simplify what I've done.

I've still to do a DELETE routine.