Code efficiency & Adding a row.

  • Thread starter Thread starter Cameron
  • Start date Start date
C

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
Click to expand...
and other Named regions within this are
Doctors = 'Extra Tables'!$C$13:$C$20
DoctorsArray = 'Extra Tables'!$C$13:$D$20
Click to expand...

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
 
Turn on the macro recorder and insert the row manually, then sort the table.

This will give you the basic code you need.
 
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
'--------------------------------------------
 
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.
 
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.
 
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.
 
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.
 
Back
Top