Expand table with VB
Hi:)
I've made simple userForm in VB to add data into a table on my sheet.
Problem i have, is when i keep adding new data to the table no borders are made.
So how can i insert a new row with borders to my table as i keep adding data ?
Im using excel 2003 and new to VB.
So if any1 could help me with this i would be Thankful:)
Here is my Vb code so far.
Private Sub CommandButtonOK_Click()
Dim emptyRow As Long
'Make Sheet1 Active
Sheets(1).Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
'Export Data to worksheet
Cells(emptyRow, 1).Value = TextNavn.Value
Cells(emptyRow, 2).Value = TextTJNummer.Value
Cells(emptyRow, 3).Value = TextTelefon.Value
Cells(emptyRow, 4).Value = TextFødselsdato.Value
If Check100.Value = True Then Cells(emptyRow, 5).Value = Check100.Caption
If Check80.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & " " & Check80.Caption
If Check50.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & " " & Check50.Caption
If CheckDeltid.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & " " & CheckDeltid.Caption
If OptionButtonMann.Value = True Then
Cells(emptyRow, 6).Value = "MANN"
End If
If OptionButtonKvinne.Value = True Then
Cells(emptyRow, 6).Value = "KVINNE"
End If
End Sub
Call the following code to
Call the following code to generate border
Sub SetBorder()
Dim rng As Range
Set rng = Sheets(1).Range("A1").CurrentRegion.Resize(, 6)
rng.Borders(xlDiagonalDown).LineStyle = xlNone
rng.Borders(xlDiagonalUp).LineStyle = xlNone
With rng.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With rng.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With rng.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With rng.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With rng.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With rng.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End Sub