Userform into multiple sheets

E

elynoy

Board Regular
Joined
Oct 29, 2018
Messages
160
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
Hello, I have this userform:
2ywg7wk.png


this is the sheet where the code works as I press one of the cells in the range. A2:A27

okyle.png



The code I have on that "Jan" sheet with the cells range for it to work.

Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Application.Intersect(Target, Range("A2:A27")) Is Nothing Then
        UserForm1.tbNumero = Cells(Target.Row, 2)
        UserForm1.tbNome = Cells(Target.Row, 3)
        UserForm1.tbTelefone = Cells(Target.Row, 4)
        UserForm1.tbTelemovel = Cells(Target.Row, 5)
        UserForm1.tbEmail = Cells(Target.Row, 6)
        UserForm1.lblRow = Target.Row
        UserForm1.Show
    End If
End Sub


Userform button code:

Code: 
Private Sub CommandButton1_Click()Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Jan")


x = Me.lblRow 'current row
ws.Cells(x, 2) = Me.tbNumero
ws.Cells(x, 3) = Me.tbNome
ws.Cells(x, 4) = Me.tbTelefone
ws.Cells(x, 5) = Me.tbTelemovel
ws.Cells(x, 6) = Me.tbEmail


Me.Hide
End Sub

I'd like to adapt this code and userform to work on the sheets "Jan", "Fev" and "Mar".. (12 months in total) and all the data will be in the same cell range wich is A7:A27 for every one of those 12 sheets. The data will be different on each sheet.

But I need this to work from a button on the "Escalas" sheet.

I tried
Code: 
userform1.show
and it works but I get error 13 type mismatch


Ex: One button on the sheet "Escalas" loads the userform and inserts the data on the A7:A27 range on sheet "Jan".
Another button on the same "Escalas" sheet loads the userform but inserts the data on range A7:A27 but for sheet "Fev"
the same for the other 10 months.

Sample file just in case anyone wants to see it.
http://s000.tinyupload.com/index.php?file_id=11610863767953451163




Best regards,
eLy
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
I forgot one thing.

When I said load it from abutton I meant that, the sheet "Escalas" have the same input as the 12 months.
For exemple this:
2hi5l5l.png


the sheet escalas have 12 months itself. and each month have that template from A to Z. I want to press A in the cell corresponding to jan and it will put the userform data into the sheet "Jan" with the Letter A.
Then, still in the sheet escalas there will be a A to Z zone corresponding to "fev". I want to press one of those letters, load the userform corresponding to that letter and put the data into the sheet "Fev" and so on.

I'm not sure if I explained myself good enought. this sounds really complicated.

Best regards,
eLy
 
Last edited:
Upvote 0
I suspect you are complicating your project or at least your description and goal.

Presently you have a macro located in the Sheet1 module. Delete that.

Paste the following in the ThisWorkbook Module :

Code: 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Not Application.Intersect(Target, Range("A2:A27")) Is Nothing Then
        UserForm1.tbNumero = Cells(Target.Row, 2)
        UserForm1.tbNome = Cells(Target.Row, 3)
        UserForm1.tbTelefone = Cells(Target.Row, 4)
        UserForm1.tbTelemovel = Cells(Target.Row, 5)
        UserForm1.tbEmail = Cells(Target.Row, 6)
        UserForm1.lblRow = Target.Row
        UserForm1.Show
    End If
End Sub

The above macro will work for all sheets - you won't have to put the same macro into each Sheet's module.

Now, when you click on a letter in Col A, the user form will appear and whatever data you enter into the textboxes will be transferred to the row
of the Col A letter you clicked.

You can add the additional sheets for each month of the year and it will function for all of those sheets as well.

If this is not what you intended ... you'll have to do a much better job of describing your goal.


Download workbook : https://www.amazon.com/clouddrive/share/tOjrA4R0DI52jBdq3BVgVXFzmtwgeXcJJ8kEoriqPZE
 
Upvote 0
Thanks for your reply. I have the code in the sheet1 because the code I found is supposed to work like that since it's a selection change on that sheet I guess.

I noticed in your sample that putting the code in thiworkbook will work on all pages indeed but I need something different.

is it possible to achieve that but for exemple puting the data instead of the active sheet, put the data on another sheet?
I need to click on cell A2 on the Sheet "Escalas"->Loads the userform-> insert data into the userform-> userform puts the data on the sheet "Jan" on cell A2.
Then, still on sheet "Escalas"-> click on cell A50->Loads the userform-> insert data into userform->userform puts the data on sheet "Fev" on cell A2.

did I explain better how i need this to work?

Best regards,
eLy
 
Upvote 0
If I put this
Code: 
Sheets("Jan").Select
it somehow works how i need it to. Is it possible to use it like this but keep the active sheet the sheet I call the userform? wich is the sheet "Escalas". I want to move to the sheet "Jan" or Feb" or whatever sheet if i go there directly.

Best regards,
eLy
 
Upvote 0
If you use the macro I posted in #3 , and pasted into the ThisWorkbook module, you will be able
to have the form appear regardless of which sheet you are viewing. Anything you enter into the form
at that point, will be transferred to the row you clicked on.

From your last description, the macro I posted in #3 does precisely what you are seeking.
 
Upvote 0
yes but I need the userform to put thedata on another sheet and not the one I call the userfom
 
Upvote 0
The way your workbook is presently designed the user has to:

Open the sheet with the command button that opens the user form.
Then click on the sheet tab where the data entry will be deposited.
Then enter the data in the user form and click OK.
The data is transferred to the sheet they previously selected.

If they desire the next data entry to be on a different sheet, they click
the tab for that sheet, then continue the process.

It might be better to have a command button on each months sheet ... somewhere at the top say row 1.
Then the user only has to click the sheet tab desired and the command button for showing the user form
is there ... no matter which sheet the user selects.


Another option would be to include a sheet selector on the User Form. That way the user will first select which
sheet they want the data to be entered into. When they select the sheet from that user form control, the sheet
auto displays. Then the user continues with the remainder of the data entry.

My personal preference would be to have a command button on each sheet. That should effectively reduce the
chance of error if the sheet selector was on the user form. I can visualize a user who is so busy entering data
they will forget to change the sheet before entering new data in the user form.

My 2 cents.
 
Upvote 0
Thanks for your suggestion but the reason why I need to to work like tis is because all the sheets are hidden. Only the main sheet wich is "Escalas" is visible. I need it to be automatic when I press A7 on the sheet escalas it loads the userform and I put the data, when I press the button add user it will put that data into another sheet wich is "Jan".

I thank you for your help. I got it to work the way I need it I just have no Idea how I did it. I just googled more codes and added them.

at the moment I have this. I think I might have a lil bit too much code since there are duplicated stuff all over the place, but it works.
Code: 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)'Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)






Dim ws As Worksheet
'Application.ScreenUpdating = False


Set ws = ActiveSheet


If Not Application.Intersect(Target, Range("AS7:AS32")) Is Nothing Then
sheets("FJaneiro").Activate
        UserForm1.tbNumero = Cells(Target.Row, 3)
        UserForm1.tbNome = Cells(Target.Row, 4)
        UserForm1.tbTelefone = Cells(Target.Row, 5)
        UserForm1.tbTelemovel = Cells(Target.Row, 6)
        UserForm1.tbEmail = Cells(Target.Row, 7)
        UserForm1.lblRow = Target.Row
        UserForm1.Show
    End If
ws.Activate


Set ws = ActiveSheet


If Not Application.Intersect(Target, Range("AS48:AS73")) Is Nothing Then
sheets("FFevereiro").Activate
        UserForm1.tbNumero = Cells(Target.Row, 3)
        UserForm1.tbNome = Cells(Target.Row, 4)
        UserForm1.tbTelefone = Cells(Target.Row, 5)
        UserForm1.tbTelemovel = Cells(Target.Row, 6)
        UserForm1.tbEmail = Cells(Target.Row, 7)
        UserForm1.lblRow = Target.Row
        UserForm1.Show
    End If
ws.Activate


Set ws = ActiveSheet


If Not Application.Intersect(Target, Range("AS88:AS113")) Is Nothing Then
sheets("FMarco").Activate
        UserForm1.tbNumero = Cells(Target.Row, 3)
        UserForm1.tbNome = Cells(Target.Row, 4)
        UserForm1.tbTelefone = Cells(Target.Row, 5)
        UserForm1.tbTelemovel = Cells(Target.Row, 6)
        UserForm1.tbEmail = Cells(Target.Row, 7)
        UserForm1.lblRow = Target.Row
        UserForm1.Show
    End If
ws.Activate




Set ws = ActiveSheet


If Not Application.Intersect(Target, Range("AS128:AS153")) Is Nothing Then
sheets("FAbril").Activate
        UserForm1.tbNumero = Cells(Target.Row, 3)
        UserForm1.tbNome = Cells(Target.Row, 4)
        UserForm1.tbTelefone = Cells(Target.Row, 5)
        UserForm1.tbTelemovel = Cells(Target.Row, 6)
        UserForm1.tbEmail = Cells(Target.Row, 7)
        UserForm1.lblRow = Target.Row
        UserForm1.Show
    End If
ws.Activate


Set ws = ActiveSheet


If Not Application.Intersect(Target, Range("AS168:AS193")) Is Nothing Then
sheets("FMaio").Activate
        UserForm1.tbNumero = Cells(Target.Row, 3)
        UserForm1.tbNome = Cells(Target.Row, 4)
        UserForm1.tbTelefone = Cells(Target.Row, 5)
        UserForm1.tbTelemovel = Cells(Target.Row, 6)
        UserForm1.tbEmail = Cells(Target.Row, 7)
        UserForm1.lblRow = Target.Row
        UserForm1.Show
    End If
ws.Activate


Set ws = ActiveSheet


If Not Application.Intersect(Target, Range("AS208:AS233")) Is Nothing Then
sheets("FJunho").Activate
        UserForm1.tbNumero = Cells(Target.Row, 3)
        UserForm1.tbNome = Cells(Target.Row, 4)
        UserForm1.tbTelefone = Cells(Target.Row, 5)
        UserForm1.tbTelemovel = Cells(Target.Row, 6)
        UserForm1.tbEmail = Cells(Target.Row, 7)
        UserForm1.lblRow = Target.Row
        UserForm1.Show
    End If
ws.Activate


Set ws = ActiveSheet


If Not Application.Intersect(Target, Range("AS248:AS273")) Is Nothing Then
sheets("FJulho").Activate
        UserForm1.tbNumero = Cells(Target.Row, 3)
        UserForm1.tbNome = Cells(Target.Row, 4)
        UserForm1.tbTelefone = Cells(Target.Row, 5)
        UserForm1.tbTelemovel = Cells(Target.Row, 6)
        UserForm1.tbEmail = Cells(Target.Row, 7)
        UserForm1.lblRow = Target.Row
        UserForm1.Show
    End If
ws.Activate


Set ws = ActiveSheet


If Not Application.Intersect(Target, Range("AS288:AS313")) Is Nothing Then
sheets("FAgosto").Activate
        UserForm1.tbNumero = Cells(Target.Row, 3)
        UserForm1.tbNome = Cells(Target.Row, 4)
        UserForm1.tbTelefone = Cells(Target.Row, 5)
        UserForm1.tbTelemovel = Cells(Target.Row, 6)
        UserForm1.tbEmail = Cells(Target.Row, 7)
        UserForm1.lblRow = Target.Row
        UserForm1.Show
    End If
ws.Activate


Set ws = ActiveSheet


If Not Application.Intersect(Target, Range("AS328:AS353")) Is Nothing Then
sheets("FSetembro").Activate
        UserForm1.tbNumero = Cells(Target.Row, 3)
        UserForm1.tbNome = Cells(Target.Row, 4)
        UserForm1.tbTelefone = Cells(Target.Row, 5)
        UserForm1.tbTelemovel = Cells(Target.Row, 6)
        UserForm1.tbEmail = Cells(Target.Row, 7)
        UserForm1.lblRow = Target.Row
        UserForm1.Show
    End If
ws.Activate


Set ws = ActiveSheet


If Not Application.Intersect(Target, Range("AS368:AS393")) Is Nothing Then
sheets("FOutubro").Activate
        UserForm1.tbNumero = Cells(Target.Row, 3)
        UserForm1.tbNome = Cells(Target.Row, 4)
        UserForm1.tbTelefone = Cells(Target.Row, 5)
        UserForm1.tbTelemovel = Cells(Target.Row, 6)
        UserForm1.tbEmail = Cells(Target.Row, 7)
        UserForm1.lblRow = Target.Row
        UserForm1.Show
    End If
ws.Activate


Set ws = ActiveSheet


If Not Application.Intersect(Target, Range("AS408:AS433")) Is Nothing Then
sheets("FNovembro").Activate
        UserForm1.tbNumero = Cells(Target.Row, 3)
        UserForm1.tbNome = Cells(Target.Row, 4)
        UserForm1.tbTelefone = Cells(Target.Row, 5)
        UserForm1.tbTelemovel = Cells(Target.Row, 6)
        UserForm1.tbEmail = Cells(Target.Row, 7)
        UserForm1.lblRow = Target.Row
        UserForm1.Show
    End If
ws.Activate


Set ws = ActiveSheet


If Not Application.Intersect(Target, Range("AS448:AS473")) Is Nothing Then
sheets("FDezembro").Activate
        UserForm1.tbNumero = Cells(Target.Row, 3)
        UserForm1.tbNome = Cells(Target.Row, 4)
        UserForm1.tbTelefone = Cells(Target.Row, 5)
        UserForm1.tbTelemovel = Cells(Target.Row, 6)
        UserForm1.tbEmail = Cells(Target.Row, 7)
        UserForm1.lblRow = Target.Row
        UserForm1.Show
    End If
ws.Activate
'Application.ScreenUpdating = True
End Sub

The only thing that got a bit messed up was that the main sheet the userform saves the data exactly on the same cell where it's clicked and put it on the sheet I need exactly on the same cell. I mean, If I press on the main sheet "escalas" cell A30, it will load the userform and put the data in sheet "Jan" on cell A30.
If I press on the main sheet "Escalas" on cell A50 the userform loads and the data I put in it will be on sheet "Fev" exactly on cell A50. Doesnt bother me so I'm keeping it like this at the moment.

Once again, thanks for your help (again)

Best regards,
eLy
 
Last edited:
Upvote 0
.
Glad you have it working the way you needed it to.

My confusion was based on the lack of notification that the sheets were hidden. That was not made known until just now ... your last post.

Also, you initially indicated you wanted to be able to click on rows A-Z and have the data transfer from the userform to the selected row.
Now in your last post you have utilized code that separates where the data is entered on the sheet by using different rows for each sheet.

For future reference, before posting anything, make certain your description is accurate and on target. Decide before you post exactly
what your goal is and state that as clearly as you can. In this example you began with one goal and ended up with something completely
different. Volunteers on this and other forums cannot provide the assistance you request if you fail to clearly state the details or
change the final product mid-stream.

Again, I am happy that you found a solution and you were able to construct the solution yourself. That adds to your knowledge of Excel and
VBA.

Cheers.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
How to return focus to Userform textbox after process is finished.
Replies
1
Views
619
jvoss
J
A
  • Solved
make faster code after increase data for 4000 rows for each sheet on userform
2
Replies
13
Views
926
Akuini
Akuini
P
  • Question
Userform Listbox Edit Rows Within Sheet
Replies
1
Views
432
pure vito
P
E
  • Question
Search function not finding rows containing the matching value
Replies
1
Views
526
GlennUK
G
S
  • Solved
Working with multiple Target Ranges in Worksheet Code
Replies
3
Views
370
sspatriots
S

Forum statistics

Threads
1,214,830
Messages
6,121,835
Members
449,051
Latest member
excelquestion515

Share this page

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