Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command button code to include worksheets_SC
In this code my range Module_2(cell name), is in a different worksheet(Path_Module). How do I declare it. This code is searching for Module_2 on the same worksheet where the command button is present. Hence i get error "Range not found". Private Sub CommandButton1_Click() Dim Rng As Range Dim Rng2 As Range Set Rng = Me.Range("Module_2") '<<=== CHANGE On Error Resume Next Set Rng2 = Rng.SpecialCells(xlCellTypeBlanks) On Error GoTo 0 If Not Rng2 Is Nothing Then MsgBox Prompt:="All of the fields " _ & "Learning Path name, Availability date and Domain" _ & " should be filled.", _ Buttons:=vbInformation, _ Title:="Missing Data" Rng2.Cells(1).Select Exit Sub End If Me.Next.Select End Sub Thanks a lot for help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command button code to include worksheets_SC
You can either reference the worksheet by tab name or directly by code name.
Tab name Set Rng = sheets("My Tab Name").Range("Module_2") Code name (if code name does not make sense just rply back... Set Rng = Sheet1.Range("Module_2") -- HTH... Jim Thomlinson "Ram" wrote: In this code my range Module_2(cell name), is in a different worksheet(Path_Module). How do I declare it. This code is searching for Module_2 on the same worksheet where the command button is present. Hence i get error "Range not found". Private Sub CommandButton1_Click() Dim Rng As Range Dim Rng2 As Range Set Rng = Me.Range("Module_2") '<<=== CHANGE On Error Resume Next Set Rng2 = Rng.SpecialCells(xlCellTypeBlanks) On Error GoTo 0 If Not Rng2 Is Nothing Then MsgBox Prompt:="All of the fields " _ & "Learning Path name, Availability date and Domain" _ & " should be filled.", _ Buttons:=vbInformation, _ Title:="Missing Data" Rng2.Cells(1).Select Exit Sub End If Me.Next.Select End Sub Thanks a lot for help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command button code to include worksheets_SC
On Jun 25, 11:01 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote: You can either reference the worksheet by tab name or directly by code name. Tab name Set Rng = sheets("My Tab Name").Range("Module_2") Code name (if code name does not make sense just rply back... Set Rng = Sheet1.Range("Module_2") -- HTH... Jim Thomlinson "Ram" wrote: In this code my range Module_2(cell name), is in a different worksheet(Path_Module). How do I declare it. This code is searching for Module_2 on the same worksheet where the command button is present. Hence i get error "Range not found". Private Sub CommandButton1_Click() Dim Rng As Range Dim Rng2 As Range Set Rng = Me.Range("Module_2") '<<=== CHANGE On Error Resume Next Set Rng2 = Rng.SpecialCells(xlCellTypeBlanks) On Error GoTo 0 If Not Rng2 Is Nothing Then MsgBox Prompt:="All of the fields " _ & "Learning Path name, Availability date and Domain" _ & " should be filled.", _ Buttons:=vbInformation, _ Title:="Missing Data" Rng2.Cells(1).Select Exit Sub End If Me.Next.Select End Sub Thanks a lot for help.- Hide quoted text - - Show quoted text - Hey Jim When I declare it this way: Set Rng = Me.Worksheets("Cert_Path_module").Range("Module_2" ), I get Method or data member not found error. Pls help i'm pretty new to coding Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command button code to include worksheets_SC
On Jun 27, 1:55 pm, Ram wrote:
On Jun 25, 11:01 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: You can either reference the worksheet by tab name or directly by code name. Tab name Set Rng = sheets("My Tab Name").Range("Module_2") Code name (if code name does not make sense just rply back... Set Rng = Sheet1.Range("Module_2") -- HTH... Jim Thomlinson "Ram" wrote: In this code my range Module_2(cell name), is in a different worksheet(Path_Module). How do I declare it. This code is searching for Module_2 on the same worksheet where the command button is present. Hence i get error "Range not found". Private Sub CommandButton1_Click() Dim Rng As Range Dim Rng2 As Range Set Rng = Me.Range("Module_2") '<<=== CHANGE On Error Resume Next Set Rng2 = Rng.SpecialCells(xlCellTypeBlanks) On Error GoTo 0 If Not Rng2 Is Nothing Then MsgBox Prompt:="All of the fields " _ & "Learning Path name, Availability date and Domain" _ & " should be filled.", _ Buttons:=vbInformation, _ Title:="Missing Data" Rng2.Cells(1).Select Exit Sub End If Me.Next.Select End Sub Thanks a lot for help.- Hide quoted text - - Show quoted text - Hey Jim When I declare it this way: Set Rng = Me.Worksheets("Cert_Path_module").Range("Module_2" ), I get Method or data member not found error. Pls help i'm pretty new to coding Thanks!- Hide quoted text - - Show quoted text - I also get error Run-time error '1004' Select method of Range class failed. When the Rng2.Cells(1).Select line is executed. If Not Rng2 Is Nothing Then MsgBox Prompt:="All of the fields " _ & "Module 2 information" _ & " should be filled.", _ Buttons:=vbInformation, _ Title:="Missing Data" Rng2.Cells(1).Select<===============Error mentioned above What changes do i have to make ?? pls help thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
command button code | Excel Discussion (Misc queries) | |||
Command button code(SC) | Excel Programming | |||
Command button code | Excel Programming | |||
Command button code | Excel Programming | |||
VBA code behind command button | Excel Worksheet Functions |