CODEWITHSUNDEEP
excelvba
Adam Walker
Adam Walker

Reputation: 56

Visual Basic - Add an entry to a list in another worksheet

I have a workbook with 2 sheets. Sheet1 is named Form and Sheet2 is named Data. I am trying to have a form on Sheet1 that creates a new row in Sheet2. I am pretty new to VBA and am really just trying to teach myself through Google. My question is, how can you reference a sheet that isnt active and have it codify the information as intended without visually switching tabs?

Any help is greatly appreciated.

Very Respectfully,

Adam Walker

Upvotes: 1

Views: 4961

Answers (4)

StoriKnow
StoriKnow

Reputation: 5867

Overkill example: here's the Form sheet with a sample form. The Submit button has been assigned the macro SubmitForm, which is a procedure created in VBA (code to follow).

Named Ranges can be very useful in VBA when looking to retrieve values from a specific cell or cells. By assigning NamedRanges to each field of the form, we can retrieve their values in VBA simply by using Range("NamedRangeName").Value

  • The First field has been assigned the NamedRange FormFirstName
  • The Last field has been assigned the NamedRange FormLastName
  • The Email field has been assigned the NamedRange FormEmail

Form

Below is what the Data sheet in my example looks like. We have a header row, then blank rows underneath it. As the Submit button on the Form sheet is clicked, a new record (row) will be created here.

Data Sheet Before Submit Clicked

Data Sheet Before

Data Sheet After Submit Clicked

Data Sheet After

And here's the SubmitForm procedure code that goes along with this sample. It does the following when a user submits the form by clicking the button:

  • Retrieve values from the Form sheet fields via the assigned NamedRanges
  • Find the next empty row on the Data sheet
  • Insert the retrieved values from the form

Public Sub SubmitForm()

Application.ScreenUpdating = False
Application.Calculation = XlCalculation.xlCalculationManual

Dim userFirstName As String
Dim userLastName As String
Dim userEmail As String

'   get a reference to the Form worksheet
Dim formWorksheet As Worksheet
Set formWorksheet = ActiveWorkbook.Worksheets("Form")

'   using the named ranges, retrieve the form field values
userFirstName = formWorksheet.Range("FormFirstName").Value
userLastName = formWorksheet.Range("FormLastName").Value
userEmail = formWorksheet.Range("FormEmail").Value

Dim dataWorksheet As Worksheet
Dim headerRow As Integer
Dim nextUserRow As Integer
Dim userIdColumn As Integer
Dim firstNameColumn As Integer
Dim lastNameColumn As Integer
Dim emailColumn As Integer
Dim createdDateColumn As Integer

'   get a reference to the Data worksheet
Set dataWorksheet = ActiveWorkbook.Worksheets("Data")
headerRow = 1

'   get the number of used rows (rows that contain data) on this worksheet,
'   then add one to it. This will be the next empty row where our new
'   record will live
nextUserRow = dataWorksheet.UsedRange.Rows.Count + 1

'   set the columns for each field
userIdColumn = 1
firstNameColumn = 2
lastNameColumn = 3
emailColumn = 4
createdDateColumn = 5

'   persist the data retrieved from the Form sheet to the Data sheet row
dataWorksheet.Cells(nextUserRow, userIdColumn).Value = (nextUserRow - headerRow)
dataWorksheet.Cells(nextUserRow, firstNameColumn).Value = userFirstName
dataWorksheet.Cells(nextUserRow, lastNameColumn).Value = userLastName
dataWorksheet.Cells(nextUserRow, emailColumn).Value = userEmail
dataWorksheet.Cells(nextUserRow, createdDateColumn).Value = Now

Application.Calculation = XlCalculation.xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

Upvotes: 0

luke_t
luke_t

Reputation: 2985

Option Explicit
Sub add_row()
    Dim wb As Workbook, ws2 As Worksheet
    Set wb = ThisWorkbook
    Set ws2 = wb.Sheets("Sheet2")
    ws2.Rows(1).Insert shift:=xlDown
End Sub

The above will add a new row at the top of sheet 2. If you have more than 1 action to perform on a different sheet, you can also use a with statement.

Option Explicit
Sub add_data()
    Dim wb As Workbook, ws2 As Worksheet
    Set wb = ThisWorkbook
    Set ws2 = wb.Sheets("Sheet2")
    With ws2
        .Rows(1).Insert shift:=xlDown
        .Cells(1, 1).Value2 = "Rows shifted down by 1!"
    End With
End Sub

I hope this is of help for you.

Upvotes: 2

Ygor Yansz
Ygor Yansz

Reputation: 176

First of all since you're new to vba you learn to declare in the right way so all people can understand what you are saying, when you say form it get to vague, is it a userform or a formula can you understand?

About the reference to another sheet that is not active just declare like this:

Workbooks("example.xlsm").Worksheets("Data").Range("A1").EntireRow.Insert

this declaration will insert a new row on the row 1, with that example you can get how to declare wich workbook, worksheet and range you want to work on

Upvotes: 1

FreeMan
FreeMan

Reputation: 5687

In general, the macro recorder will teach you how to do things. However, it does write very obtuse code and always works with Active* (Cell, Range, WorkSheet, etc) by using .select, which you've figured out, isn't very efficient or pretty.

Try this:

ActiveWorkbook.worksheets("Form").range("A1") = "This goes on the Form sheet"
ActiveWorkbook.worksheet("Data").range("A1") = "This goes on the Data sheet"

Of course, that relies on ActiveWorkbook, but you get the idea.

Upvotes: 1

Related Questions