using a user form for more than 1 sheet in a workbook

lilhotch14

Board Regular
Joined
Sep 3, 2006
Messages
75
im wondering how to use the same user form for to sheets, but have the code change for the button on each sheet. can anyone help?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
If you have programed the userform code correctly it should automatically update the active sheet.
If you have coded using the
Activesheet
object, and didn't hardcode your sheet references, you shouldn't have a problem. Post the userform code and we can take a look at it and let you know what to change.

HTH
Cal
 
Upvote 0
here is the code for button on the userform

Application.ScreenUpdating = False
Range("B6:AK6").Select
Selection.ClearContents
Range("B7:b8").Select
Selection.ClearContents
Range("B9:AK9").Select
Selection.ClearContents
Range("B11:AK11").Select
Selection.ClearContents
Range("b14").Select
Selection.ClearContents
Range("B15:AK15").Select
Selection.ClearContents
Range("B17:AK17").Select
Selection.ClearContents
Range("B20").Select
Selection.ClearContents
Range("B21:AK21").Select
Selection.ClearContents
Range("B23:AK23").Select
Selection.ClearContents
Range("B27:b28").Select
Selection.ClearContents
Range("B29:AK29").Select
Selection.ClearContents
Range("B31:AK31").Select
Selection.ClearContents
Range("b34").Select
Selection.ClearContents
Range("B35:AK35").Select
Selection.ClearContents
Range("B37:AK37").Select
Selection.ClearContents
Range("B40").Select
Selection.ClearContents
Range("B41:AK41").Select
Selection.ClearContents
Range("B43:AK43").Select
Selection.ClearContents
Range("B47:b48").Select
Selection.ClearContents
Range("B49:AK49").Select
Selection.ClearContents
Range("B51:AK51").Select
Selection.ClearContents
Range("b54").Select
Selection.ClearContents
Range("B55:AK55").Select
Selection.ClearContents
Range("B57:AK57").Select
Selection.ClearContents
Range("B60").Select
Selection.ClearContents
Range("B61:AK61").Select
Selection.ClearContents
Range("B63:AK63").Select
Selection.ClearContents
Range("B67:b68").Select
Selection.ClearContents
Range("B69:AK69").Select
Selection.ClearContents
Range("B71:AK71").Select
Selection.ClearContents
Range("b74").Select
Selection.ClearContents
Range("B75:AK75").Select
Selection.ClearContents
Range("B77:AK77").Select
Selection.ClearContents
Range("B80").Select
Selection.ClearContents
Range("B81:AK81").Select
Selection.ClearContents
Range("B83:AK83").Select
Selection.ClearContents
Range("B107:b108").Select
Selection.ClearContents
Range("B109:AK109").Select
Selection.ClearContents
Range("B111:AK111").Select
Selection.ClearContents
Range("b114").Select
Selection.ClearContents
Range("B115:AK115").Select
Selection.ClearContents
Range("B117:AK117").Select
Selection.ClearContents
Range("B120").Select
Selection.ClearContents
Range("B121:AK121").Select
Selection.ClearContents
Range("B123:AK123").Select
Selection.ClearContents
Range("B127:b128").Select
Selection.ClearContents
Range("B129:AK129").Select
Selection.ClearContents
Range("B131:AK131").Select
Selection.ClearContents
Range("b134").Select
Selection.ClearContents
Range("B135:AK135").Select
Selection.ClearContents
Range("B137:AK137").Select
Selection.ClearContents
Range("B140").Select
Selection.ClearContents
Range("B141:AK141").Select
Selection.ClearContents
Range("B143:AK143").Select
Selection.ClearContents
Range("B147:b148").Select
Selection.ClearContents
Range("B149:AK149").Select
Selection.ClearContents
Range("B151:AK151").Select
Selection.ClearContents
Range("b154").Select
Selection.ClearContents
Range("B155:AK155").Select
Selection.ClearContents
Range("B157:AK157").Select
Selection.ClearContents
Range("B160").Select
Selection.ClearContents
Range("B161:AK161").Select
Selection.ClearContents
Range("B163:AK163").Select
Range("B167:b168").Select
Selection.ClearContents
Range("B169:AK169").Select
Selection.ClearContents
Range("B171:AK171").Select
Selection.ClearContents
Range("b174").Select
Selection.ClearContents
Range("B175:AK175").Select
Selection.ClearContents
Range("B177:AK177").Select
Selection.ClearContents
Range("B180").Select
Selection.ClearContents
Range("B181:AK181").Select
Selection.ClearContents
Range("B183:AK183").Select
Selection.ClearContents
Range("B207:b208").Select
Selection.ClearContents
Range("B209:AK209").Select
Selection.ClearContents
Range("B211:AK211").Select
Selection.ClearContents
Range("b214").Select
Selection.ClearContents
Range("B215:AK215").Select
Selection.ClearContents
Range("B217:AK217").Select
Selection.ClearContents
Range("B220").Select
Selection.ClearContents
Range("B221:AK221").Select
Selection.ClearContents
Range("B223:AK223").Select
Selection.ClearContents
Range("B227:b228").Select
Selection.ClearContents
Range("B229:AK229").Select
Selection.ClearContents
Range("B231:AK231").Select
Selection.ClearContents
Range("b234").Select
Selection.ClearContents
Range("B235:AK235").Select
Selection.ClearContents
Range("B237:AK237").Select
Selection.ClearContents
Range("B240").Select
Selection.ClearContents
Range("B241:AK241").Select
Selection.ClearContents
Range("B243:AK243").Select
Selection.ClearContents
UserForm1.hide
UserForm3.show

most of the ranges change on the new sheet though
 
Upvote 0
Most of them change? Then you will need to do something like this.

Select Case activesheet.name

case "Sheet1"
Your Code.
case "Sheet2"
Your new code

end select.

HTH
Cal
 
Upvote 0
here is my new code and my other 1 along w/ the case select u gave me.

Application.ScreenUpdating = False
Select Case .ActiveSheet.Name
Case "Bantam"
Range("B6:AK6").Select
Selection.ClearContents
Range("B7:b8").Select
Selection.ClearContents
Range("B9:AK9").Select
Selection.ClearContents
Range("B11:AK11").Select
Selection.ClearContents
Range("b14").Select
Selection.ClearContents
Range("B15:AK15").Select
Selection.ClearContents
Range("B17:AK17").Select
Selection.ClearContents
Range("B21:b22").Select
Selection.ClearContents
Range("B23:AK23").Select
Selection.ClearContents
Range("B25:AK25").Select
Selection.ClearContents
Range("b28").Select
Selection.ClearContents
Range("B29:AK29").Select
Selection.ClearContents
Range("B31:AK31").Select
Selection.ClearContents
Range("b35:b36").Select
Selection.ClearContents
Range("B37:AK37").Select
Selection.ClearContents
Range("B39:AK39").Select
Selection.ClearContents
Range("B42").Select
Selection.ClearContents
Range("B43:AK43").Select
Selection.ClearContents
Range("B45:AK45").Select
Selection.ClearContents
Range("B49:b50").Select
Selection.ClearContents
Range("B51:AK51").Select
Selection.ClearContents
Range("B53:AK53").Select
Selection.ClearContents
Range("b56").Select
Selection.ClearContents
Range("B57:AK57").Select
Selection.ClearContents
Range("B59:AK59").Select
Selection.ClearContents
Range("B63:b64").Select
Selection.ClearContents
Range("B35:AK65").Select
Selection.ClearContents
Range("B67:AK67").Select
Selection.ClearContents
Range("B70").Select
Selection.ClearContents
Range("B71:AK71").Select
Selection.ClearContents
Range("B73:AK73").Select
Selection.ClearContents
Range("b77:b78").Select
Selection.ClearContents
Range("B79:AK79").Select
Selection.ClearContents
Range("B81:AK81").Select
Selection.ClearContents
Range("B84").Select
Selection.ClearContents
Range("B85:AK85").Select
Selection.ClearContents
Range("B87:AK87").Select
Selection.ClearContents
Range("B91:b92").Select
Selection.ClearContents
Range("B93:AK93").Select
Selection.ClearContents
Range("B95:AK95").Select
Selection.ClearContents
Range("b98").Select
Selection.ClearContents
Range("B9:AK99").Select
Selection.ClearContents
Range("B101:AK101").Select
Selection.ClearContents
Range("B105:106").Select
Selection.ClearContents
Range("B107:AK107").Select
Selection.ClearContents
Range("B109:AK109").Select
Selection.ClearContents
Range("B112").Select
Selection.ClearContents
Range("B113:AK113").Select
Selection.ClearContents
Range("B115:AK115").Select
Selection.ClearContents
Range("b119:b120").Select
Selection.ClearContents
Range("B121:AK121").Select
Selection.ClearContents
Range("B123:AK123").Select
Selection.ClearContents
Range("B126").Select
Selection.ClearContents
Range("B127:AK127").Select
Selection.ClearContents
Range("B129:AK129").Select
Selection.ClearContents
Range("B133:b134").Select
Selection.ClearContents
Range("B135:AK135").Select
Selection.ClearContents
Range("B137:AK137").Select
Selection.ClearContents
Range("b140").Select
Selection.ClearContents
Range("B141:AK141").Select
Selection.ClearContents
Range("B143:AK143").Select
Selection.ClearContents
Range("B147:b148").Select
Selection.ClearContents
Range("B149:AK149").Select
Selection.ClearContents
Range("B151:AK151").Select
Selection.ClearContents
Range("B154").Select
Selection.ClearContents
Range("B155:AK155").Select
Selection.ClearContents
Range("B157:AK157").Select
Selection.ClearContents
Range("b161:b162").Select
Selection.ClearContents
Range("B163:AK163").Select
Selection.ClearContents
Range("B165:AK165").Select
Selection.ClearContents
Range("B168").Select
Selection.ClearContents
Range("B169:AK169").Select
Selection.ClearContents
Range("B171:AK171").Select
Selection.ClearContents
UserForm1.hide
UserForm3.show
Case "peewee"
Application.ScreenUpdating = False
Range("B6:AK6").Select
Selection.ClearContents
Range("B7:b8").Select
Selection.ClearContents
Range("B9:AK9").Select
Selection.ClearContents
Range("B11:AK11").Select
Selection.ClearContents
Range("b14").Select
Selection.ClearContents
Range("B15:AK15").Select
Selection.ClearContents
Range("B17:AK17").Select
Selection.ClearContents
Range("B20").Select
Selection.ClearContents
Range("B21:AK21").Select
Selection.ClearContents
Range("B23:AK23").Select
Selection.ClearContents
Range("B27:b28").Select
Selection.ClearContents
Range("B29:AK29").Select
Selection.ClearContents
Range("B31:AK31").Select
Selection.ClearContents
Range("b34").Select
Selection.ClearContents
Range("B35:AK35").Select
Selection.ClearContents
Range("B37:AK37").Select
Selection.ClearContents
Range("B40").Select
Selection.ClearContents
Range("B41:AK41").Select
Selection.ClearContents
Range("B43:AK43").Select
Selection.ClearContents
Range("B47:b48").Select
Selection.ClearContents
Range("B49:AK49").Select
Selection.ClearContents
Range("B51:AK51").Select
Selection.ClearContents
Range("b54").Select
Selection.ClearContents
Range("B55:AK55").Select
Selection.ClearContents
Range("B57:AK57").Select
Selection.ClearContents
Range("B60").Select
Selection.ClearContents
Range("B61:AK61").Select
Selection.ClearContents
Range("B63:AK63").Select
Selection.ClearContents
Range("B67:b68").Select
Selection.ClearContents
Range("B69:AK69").Select
Selection.ClearContents
Range("B71:AK71").Select
Selection.ClearContents
Range("b74").Select
Selection.ClearContents
Range("B75:AK75").Select
Selection.ClearContents
Range("B77:AK77").Select
Selection.ClearContents
Range("B80").Select
Selection.ClearContents
Range("B81:AK81").Select
Selection.ClearContents
Range("B83:AK83").Select
Selection.ClearContents
Range("B107:b108").Select
Selection.ClearContents
Range("B109:AK109").Select
Selection.ClearContents
Range("B111:AK111").Select
Selection.ClearContents
Range("b114").Select
Selection.ClearContents
Range("B115:AK115").Select
Selection.ClearContents
Range("B117:AK117").Select
Selection.ClearContents
Range("B120").Select
Selection.ClearContents
Range("B121:AK121").Select
Selection.ClearContents
Range("B123:AK123").Select
Selection.ClearContents
Range("B127:b128").Select
Selection.ClearContents
Range("B129:AK129").Select
Selection.ClearContents
Range("B131:AK131").Select
Selection.ClearContents
Range("b134").Select
Selection.ClearContents
Range("B135:AK135").Select
Selection.ClearContents
Range("B137:AK137").Select
Selection.ClearContents
Range("B140").Select
Selection.ClearContents
Range("B141:AK141").Select
Selection.ClearContents
Range("B143:AK143").Select
Selection.ClearContents
Range("B147:b148").Select
Selection.ClearContents
Range("B149:AK149").Select
Selection.ClearContents
Range("B151:AK151").Select
Selection.ClearContents
Range("b154").Select
Selection.ClearContents
Range("B155:AK155").Select
Selection.ClearContents
Range("B157:AK157").Select
Selection.ClearContents
Range("B160").Select
Selection.ClearContents
Range("B161:AK161").Select
Selection.ClearContents
Range("B163:AK163").Select
Range("B167:b168").Select
Selection.ClearContents
Range("B169:AK169").Select
Selection.ClearContents
Range("B171:AK171").Select
Selection.ClearContents
Range("b174").Select
Selection.ClearContents
Range("B175:AK175").Select
Selection.ClearContents
Range("B177:AK177").Select
Selection.ClearContents
Range("B180").Select
Selection.ClearContents
Range("B181:AK181").Select
Selection.ClearContents
Range("B183:AK183").Select
Selection.ClearContents
Range("B207:b208").Select
Selection.ClearContents
Range("B209:AK209").Select
Selection.ClearContents
Range("B211:AK211").Select
Selection.ClearContents
Range("b214").Select
Selection.ClearContents
Range("B215:AK215").Select
Selection.ClearContents
Range("B217:AK217").Select
Selection.ClearContents
Range("B220").Select
Selection.ClearContents
Range("B221:AK221").Select
Selection.ClearContents
Range("B223:AK223").Select
Selection.ClearContents
Range("B227:b228").Select
Selection.ClearContents
Range("B229:AK229").Select
Selection.ClearContents
Range("B231:AK231").Select
Selection.ClearContents
Range("b234").Select
Selection.ClearContents
Range("B235:AK235").Select
Selection.ClearContents
Range("B237:AK237").Select
Selection.ClearContents
Range("B240").Select
Selection.ClearContents
Range("B241:AK241").Select
Selection.ClearContents
Range("B243:AK243").Select
Selection.ClearContents
UserForm1.hide
UserForm3.show
End Select



im getting a complie error w/ .ActiveSheet. highlighted

am i missing something in the code?
 
Upvote 0
lilhotch14,
Looks like you made a typo. It should be

Select Case Activesheet.name

Not
Select Case .activesheet.name

HTH
Cal
 
Upvote 0
lilhotch14

In the code posted every time you have something like this.
Code:
 Range("b14").Select
Selection.ClearContents
You can replace it with this.
Code:
 Range("b14").ClearContents
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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