UserForm activating the sheet that gets updated!

Dash12

Board Regular
Joined
Jun 17, 2015
Messages
125
I was able to develop a userform but haven't been able to surpass one issue. I have two sheets in the workbook. The userform opens up when an event is triggered on the sheet 1. The userform updates the second sheet but only by opening it. For example: when I hit add button in userform while on sheet 1 , it won't update sheet 2 . It updates sheet 2 only when I go to sheet 2 and press add button again. Could you provide some suggestion how to fix that issue? I want to update sheet 2 without the screen switching to sheet 2 from sheet 1. Thank you
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi welcome to the board.
You need to qualify the range to the sheet in your code. So for example:

Code:
Sheets("Sheet2").Cells(2, 1).Value = "Hello"

This will place word “Hello” in row 2 column 1 even if Sheet2 is not the active sheet.

If unsure how to adjust your code, post it on the board, plenty here to offer assistance.

Dave
 
Upvote 0
Yes I understand that part. So, my userform starts adding data at a specific row in sheet 2 and then goes on from there, it is actually creating a dynamic list there. Each row has about 6 columns namely sku, description, watt, cost, install price,lifespan which all get filled in my the userform. How do I use your code ? Would I need to write a macro for to update each cell? Here is the macro which triggers the userform:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("$C$7:$C$65")) Is Nothing And Target.Value = "Custom" Then
        UserForm1.Show vbModeless
        Sheets("Sheet2").Select
    End If
End Sub

The above code looks for the word custom in the respective cell range and if it is there opens up the userform. I wrote this macro on Sheet 1 though because that is where I want user to be adding the data from, but this code takes the user to sheet 2 and then adds the data. Now I know it is happening because I have sheets("sheet2").select in there for now to make it work, but I do not wish to see sheet 2 but only update it!

Hi welcome to the board.
You need to qualify the range to the sheet in your code. So for example:

Code:
Sheets("Sheet2").Cells(2, 1).Value = "Hello"

This will place word “Hello” in row 2 column 1 even if Sheet2 is not the active sheet.

If unsure how to adjust your code, post it on the board, plenty here to offer assistance.

Dave
 
Last edited:
Upvote 0
Hi,
post code from your Form that writes data to your worksheet & I will update it.

Dave
 
Upvote 0
Code:
Private Sub cmdAdd_Click()
Dim lastrow As Long
    lastrow = Sheets("Sheet2").Range("Y" & Rows.Count).End(xlUp).Offset(1, 0).Row
    Cells(lastrow, "Y").Value = txtSKU.Text
    Cells(lastrow, "W").Value = txtProduct.Text
    Cells(lastrow, "X").Value = txtDescription.Text
    Cells(lastrow, "Z").Value = txtLifespan.Text
    Cells(lastrow, "AB").Value = txtWattage.Text
    Cells(lastrow, "AH").Value = txtPrice.Text
    Cells(lastrow, "AI").Value = txtUnitInstallPrice.Text
    Cells(lastrow, "AA").Value = 1
    If ComboBox1.Text = "PRPA: 0.75" Then
        Cells(lastrow, "AJ").Value = 0.75
    ElseIf ComboBox1.Text = "PRPA: 0.25" Then
        Cells(lastrow, "AJ").Value = 0.25
    ElseIf ComboBox1.Text = "Xcel" Then
        Cells(lastrow, "AK").Value = 0.4
    ElseIf ComboBox1.Text = "ERA" Then
        Cells(lastrow, "AL").Value = 0.5
    End If
End Sub

Yes I understand that part. So, my userform starts adding data at a specific row in sheet 2 and then goes on from there, it is actually creating a dynamic list there. Each row has about 6 columns namely sku, description, watt, cost, install price,lifespan which all get filled in my the userform. How do I use your code ? Would I need to write a macro for to update each cell? Here is the macro which triggers the userform:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("$C$7:$C$65")) Is Nothing And Target.Value = "Custom" Then
        UserForm1.Show vbModeless
        Sheets("Sheet2").Select
    End If
End Sub

The above code looks for the word custom in the respective cell range and if it is there opens up the userform. I wrote this macro on Sheet 1 though because that is where I want user to be adding the data from, but this code takes the user to sheet 2 and then adds the data. Now I know it is happening because I have sheets("sheet2").select in there for now to make it work, but I do not wish to see sheet 2 but only update it!
 
Upvote 0
try this:

Code:
Private Sub cmdAdd_Click()
    Dim lastrow As Long
    
    With Worksheets("Sheet2")
        lastrow = .Range("Y" & .Rows.Count).End(xlUp).Offset(1, 0).Row
        
        .Cells(lastrow, "Y").Value = txtSKU.Text
        .Cells(lastrow, "W").Value = txtProduct.Text
        .Cells(lastrow, "X").Value = txtDescription.Text
        .Cells(lastrow, "Z").Value = txtLifespan.Text
        .Cells(lastrow, "AB").Value = txtWattage.Text
        .Cells(lastrow, "AH").Value = txtPrice.Text
        .Cells(lastrow, "AI").Value = txtUnitInstallPrice.Text
        .Cells(lastrow, "AA").Value = 1
        
        If ComboBox1.Text = "PRPA: 0.75" Then
            .Cells(lastrow, "AJ").Value = 0.75
        ElseIf ComboBox1.Text = "PRPA: 0.25" Then
            .Cells(lastrow, "AJ").Value = 0.25
        ElseIf ComboBox1.Text = "Xcel" Then
            .Cells(lastrow, "AK").Value = 0.4
        ElseIf ComboBox1.Text = "ERA" Then
            .Cells(lastrow, "AL").Value = 0.5
        End If
        
    End With
    
End Sub

You can remove this line

Sheets("Sheet2").Select

from your worksheet_change event code.

Dave
 
Upvote 0
I tried this code but it doesn't update sheet 2 . I didn't get any error, but the userform stayed on sheet 1 and there was no update done to sheet 2, until i went to sheet 2 and hit the add button again...:confused:

try this:

Code:
Private Sub cmdAdd_Click()
    Dim lastrow As Long
    
    With Worksheets("Sheet2")
        lastrow = .Range("Y" & .Rows.Count).End(xlUp).Offset(1, 0).Row
        
        .Cells(lastrow, "Y").Value = txtSKU.Text
        .Cells(lastrow, "W").Value = txtProduct.Text
        .Cells(lastrow, "X").Value = txtDescription.Text
        .Cells(lastrow, "Z").Value = txtLifespan.Text
        .Cells(lastrow, "AB").Value = txtWattage.Text
        .Cells(lastrow, "AH").Value = txtPrice.Text
        .Cells(lastrow, "AI").Value = txtUnitInstallPrice.Text
        .Cells(lastrow, "AA").Value = 1
        
        If ComboBox1.Text = "PRPA: 0.75" Then
            .Cells(lastrow, "AJ").Value = 0.75
        ElseIf ComboBox1.Text = "PRPA: 0.25" Then
            .Cells(lastrow, "AJ").Value = 0.25
        ElseIf ComboBox1.Text = "Xcel" Then
            .Cells(lastrow, "AK").Value = 0.4
        ElseIf ComboBox1.Text = "ERA" Then
            .Cells(lastrow, "AL").Value = 0.5
        End If
        
    End With
    
End Sub

You can remove this line

Sheets("Sheet2").Select

from your worksheet_change event code.

Dave
 
Upvote 0
Hi,
if you have replaced your existing code in your Form with suggested updated code then it should work as required.

If still having problems, place copy of your workbook in a dropbox with link to it.

Dave
 
Upvote 0
I can't do that as its company property. I have tried what you suggested and it didn't update sheet 2 when I put the code in userform addbutton

Hi,
if you have replaced your existing code in your Form with suggested updated code then it should work as required.

If still having problems, place copy of your workbook in a dropbox with link to it.

Dave
 
Upvote 0
Hi, I found the error on my part. I forgot to put . before cells, now it is working. Thank you so much

I can't do that as its company property. I have tried what you suggested and it didn't update sheet 2 when I put the code in userform addbutton
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top