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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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
 

lilhotch14

Board Regular
Joined
Sep 3, 2006
Messages
75
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
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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
 

lilhotch14

Board Regular
Joined
Sep 3, 2006
Messages
75

ADVERTISEMENT

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?
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
lilhotch14,
Looks like you made a typo. It should be

Select Case Activesheet.name

Not
Select Case .activesheet.name

HTH
Cal
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,305
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,144,394
Messages
5,724,087
Members
422,536
Latest member
Zeeshan53

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
Top