macro for cell border only works separately

[nikaleya]

Hi everyone,

I'm currently creating an input form for projects. The idea is to save the data put in (sheet "form") to a database (sheet "database") via VBA. To prevent the cells in the database to get formatted in any way, I only copy the values from the form. So far, so good. To have a "clean" database only the cells with values should have a border around. To try this, I created a separate macro, and this works fine. If I copy the code to the main sub for "copytodatabase", I get an error: Method 'Range' of object '_Worksheet' failed.

Code for the database:

Sub copytodatabase()

Dim wb As Workbook: Set wb = ThisWorkbook
Dim form As Worksheet: Set form = wb.Worksheets("Form")
Dim database As Worksheet: Set database = wb.Worksheets("Database")
Dim range As String
Dim column As Integer
Dim lastentry, newentry As Integer

lastentry = database.Cells(Rows.Count, 1).End(xlUp).Row '(last row in database)
newentry = lastentry + 1 '(row for new entry)

'Project Number
range = "Input_ProjectNumber"
column = 1
database.Cells(newentry, column).Value = form.range(range).Value
'...and so on

Code for formatting the cells:

Sub formatdatabase()

Dim wb As Workbook: Set wb = ThisWorkbook
Dim database As Worksheet: Set database = wb.Worksheets("Database")
Dim lastrow As Integer

lastrow = database.Cells(Rows.Count, 1).End(xlUp).Row

'Disable wrap text
database.range(Cells(2, 1), Cells(lastrow, 40)).WrapText = False

'cell borders
With database.range(Cells(2, 1), Cells(lastrow, 40))
    .Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous
    .Borders(xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous
    .Borders(xlEdgeRight).LineStyle = XlLineStyle.xlContinuous
    .Borders(xlEdgeTop).LineStyle = XlLineStyle.xlContinuous
    .Borders(xlInsideVertical).LineStyle = XlLineStyle.xlContinuous
    .Borders(xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous
    
End With

If I first run "copytodase" and then "formatdatabase" everything works and there is no error. If I use the call command within the first code, I get the error. Why is this happening? What am I missing?

Many thanks for your help!

Nika

 

[Yongle]

Try this

Sub formatdatabase()

Dim wb As Workbook: Set wb = ThisWorkbook
Dim database As Worksheet: Set database = wb.Worksheets("Database")
Dim lastrow As Integer

lastrow = database.Cells(database.Rows.Count, 1).End(xlUp).Row

'Disable wrap text
database.Range(database.Cells(2, 1), database.Cells(lastrow, 40)).WrapText = False

'cell borders
With database.Range(database.Cells(2, 1), database.Cells(lastrow, 40))
    .Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous
    .Borders(xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous
    .Borders(xlEdgeRight).LineStyle = XlLineStyle.xlContinuous
    .Borders(xlEdgeTop).LineStyle = XlLineStyle.xlContinuous
    .Borders(xlInsideVertical).LineStyle = XlLineStyle.xlContinuous
    .Borders(xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous
End With

End Sub

I suspect that sheet "Database" is not the active sheet when the macro is run - so VBA needs additional help

 

[nikaleya]

I knew I was missing something!
Many thanks, works perfectly!

 

[MARK858]

@nikaleya, you can shorten all those Borders lines down to

database.Range(database.Cells(2, 1), database.Cells(lastrow, 40)).Borders.LineStyle = xlContinuous

 

[nikaleya]

@MARK858 that's indeed quite shorter, thank you!

 

[MARK858]

You're welcome