Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another "Object doesn't support..."
I have this code (still under development):
Private Sub Workbook_SheetChange(ByVal wsShName As Object, ByVal Target As Range) If Right(wsShName.Name, 7) < "Monthly" Then 'ignore these sheeets Select Case wsShName.Name Case "(Code Key)" 'Excluded sheets Exit Sub Case Else 'if password hasn't been entered yet, then ask for password If bPwrdEntrd = False Then Call PasswordEntry End If End Select End If End Sub Sub PasswordEntry() Dim rFoundShName As Range Dim rShNames As Range Dim wsPwrdNames As Worksheet 'Dim sPwrd As String Set wsPwrdNames = ThisWorkbook.Sheets("sheet1") Set rShNames = wsPwrdNames.Range("A1:A8") wsPwrdNames.Visible = True ufPwrdEntry.Show Set rFoundShName = rShNames.Find(ActiveSheet.Name, _ LookIn:=xlValues, _ LookAt:=xlWhole) If sPwrd = rFoundShName.Offset(1, 0).Value Then bPwrdEntrd = True End If wsPwrdNames.Visible = False End Sub And this code in the userform: Sub cbOK_Click() sPwrd = ufPwrdEntry.tbPwrdEntry.Text <---- OBJECT DOESN'T SUPPORT THIS.... If sPwrd = "" Then MsgBox "Please enter the password to access this worksheet.", vbOKCancel End If End Sub I tried changing the .text to .value, but that didn't help. Any ideas on why? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another "Object doesn't support..."
First, I'm not sure why you changed the workbook_sheetchange arguments from:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Second, I don't see where you declared sPwrd. Is that in a General module like: Public sPwrd as string Third, check your typing. Since the routine is behind the UserForm, I think I'd use code like: sPwrd = me.tbPwrdEntry.Text (Me is the object that owns the code--in this case, the userform.) Are you sure tbPwrdEntry is really a textbox? davegb wrote: I have this code (still under development): Private Sub Workbook_SheetChange(ByVal wsShName As Object, ByVal Target As Range) If Right(wsShName.Name, 7) < "Monthly" Then 'ignore these sheeets Select Case wsShName.Name Case "(Code Key)" 'Excluded sheets Exit Sub Case Else 'if password hasn't been entered yet, then ask for password If bPwrdEntrd = False Then Call PasswordEntry End If End Select End If End Sub Sub PasswordEntry() Dim rFoundShName As Range Dim rShNames As Range Dim wsPwrdNames As Worksheet 'Dim sPwrd As String Set wsPwrdNames = ThisWorkbook.Sheets("sheet1") Set rShNames = wsPwrdNames.Range("A1:A8") wsPwrdNames.Visible = True ufPwrdEntry.Show Set rFoundShName = rShNames.Find(ActiveSheet.Name, _ LookIn:=xlValues, _ LookAt:=xlWhole) If sPwrd = rFoundShName.Offset(1, 0).Value Then bPwrdEntrd = True End If wsPwrdNames.Visible = False End Sub And this code in the userform: Sub cbOK_Click() sPwrd = ufPwrdEntry.tbPwrdEntry.Text <---- OBJECT DOESN'T SUPPORT THIS.... If sPwrd = "" Then MsgBox "Please enter the password to access this worksheet.", vbOKCancel End If End Sub I tried changing the .text to .value, but that didn't help. Any ideas on why? Thanks! -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another "Object doesn't support..."
On Jan 30, 1:21 pm, Dave Peterson wrote:
First, I'm not sure why you changed the workbook_sheetchange arguments from: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Thanks for your reply. I didn't think they had to be by those specific names. I changed them to a format I use so I can tell what kind of variable it is. I changed them back. Second, I don't see where you declared sPwrd. Is that in a General module like: Public sPwrd as string sPwrd is declared in another general module as you've described. Third, check your typing. Since the routine is behind the UserForm, I think I'd use code like: sPwrd = me.tbPwrdEntry.Text I used the name of the form rather than "me". I don't really understand the "me" thing, but it worked! (Me is the object that owns the code--in this case, the userform.) Are you sure tbPwrdEntry is really a textbox? davegb wrote: I have this code (still under development): Private Sub Workbook_SheetChange(ByVal wsShName As Object, ByVal Target As Range) If Right(wsShName.Name, 7) < "Monthly" Then 'ignore these sheeets Select Case wsShName.Name Case "(Code Key)" 'Excluded sheets Exit Sub Case Else 'if password hasn't been entered yet, then ask for password If bPwrdEntrd = False Then Call PasswordEntry End If End Select End If End Sub Sub PasswordEntry() Dim rFoundShName As Range Dim rShNames As Range Dim wsPwrdNames As Worksheet 'Dim sPwrd As String Set wsPwrdNames = ThisWorkbook.Sheets("sheet1") Set rShNames = wsPwrdNames.Range("A1:A8") wsPwrdNames.Visible = True ufPwrdEntry.Show Set rFoundShName = rShNames.Find(ActiveSheet.Name, _ LookIn:=xlValues, _ LookAt:=xlWhole) If sPwrd = rFoundShName.Offset(1, 0).Value Then bPwrdEntrd = True End If wsPwrdNames.Visible = False End Sub And this code in the userform: Sub cbOK_Click() sPwrd = ufPwrdEntry.tbPwrdEntry.Text <---- OBJECT DOESN'T SUPPORT THIS.... If sPwrd = "" Then MsgBox "Please enter the password to access this worksheet.", vbOKCancel End If End Sub I tried changing the .text to .value, but that didn't help. Any ideas on why? Thanks! -- Dave Peterson- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another "Object doesn't support..."
They can be changed--there's someone who changes them so that they match company
standards--but it's pretty unusual. I'd guess that you misspelled the name of the userform if the "me." stuff worked. davegb wrote: On Jan 30, 1:21 pm, Dave Peterson wrote: First, I'm not sure why you changed the workbook_sheetchange arguments from: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Thanks for your reply. I didn't think they had to be by those specific names. I changed them to a format I use so I can tell what kind of variable it is. I changed them back. Second, I don't see where you declared sPwrd. Is that in a General module like: Public sPwrd as string sPwrd is declared in another general module as you've described. Third, check your typing. Since the routine is behind the UserForm, I think I'd use code like: sPwrd = me.tbPwrdEntry.Text I used the name of the form rather than "me". I don't really understand the "me" thing, but it worked! (Me is the object that owns the code--in this case, the userform.) Are you sure tbPwrdEntry is really a textbox? davegb wrote: I have this code (still under development): Private Sub Workbook_SheetChange(ByVal wsShName As Object, ByVal Target As Range) If Right(wsShName.Name, 7) < "Monthly" Then 'ignore these sheeets Select Case wsShName.Name Case "(Code Key)" 'Excluded sheets Exit Sub Case Else 'if password hasn't been entered yet, then ask for password If bPwrdEntrd = False Then Call PasswordEntry End If End Select End If End Sub Sub PasswordEntry() Dim rFoundShName As Range Dim rShNames As Range Dim wsPwrdNames As Worksheet 'Dim sPwrd As String Set wsPwrdNames = ThisWorkbook.Sheets("sheet1") Set rShNames = wsPwrdNames.Range("A1:A8") wsPwrdNames.Visible = True ufPwrdEntry.Show Set rFoundShName = rShNames.Find(ActiveSheet.Name, _ LookIn:=xlValues, _ LookAt:=xlWhole) If sPwrd = rFoundShName.Offset(1, 0).Value Then bPwrdEntrd = True End If wsPwrdNames.Visible = False End Sub And this code in the userform: Sub cbOK_Click() sPwrd = ufPwrdEntry.tbPwrdEntry.Text <---- OBJECT DOESN'T SUPPORT THIS.... If sPwrd = "" Then MsgBox "Please enter the password to access this worksheet.", vbOKCancel End If End Sub I tried changing the .text to .value, but that didn't help. Any ideas on why? Thanks! -- Dave Peterson- Hide quoted text - - Show quoted text - -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another "Object doesn't support..."
On Jan 30, 2:59 pm, Dave Peterson wrote:
They can be changed--there's someone who changes them so that they match company standards--but it's pretty unusual. I'd guess that you misspelled the name of the userform if the "me." stuff worked. I checked it about 10 times, but you never know... Thanks again, Dave! davegb wrote: On Jan 30, 1:21 pm, Dave Peterson wrote: First, I'm not sure why you changed the workbook_sheetchange arguments from: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Thanks for your reply. I didn't think they had to be by those specific names. I changed them to a format I use so I can tell what kind of variable it is. I changed them back. Second, I don't see where you declared sPwrd. Is that in a General module like: Public sPwrd as string sPwrd is declared in another general module as you've described. Third, check your typing. Since the routine is behind the UserForm, I think I'd use code like: sPwrd = me.tbPwrdEntry.Text I used the name of the form rather than "me". I don't really understand the "me" thing, but it worked! (Me is the object that owns the code--in this case, the userform.) Are you sure tbPwrdEntry is really a textbox? davegb wrote: I have this code (still under development): Private Sub Workbook_SheetChange(ByVal wsShName As Object, ByVal Target As Range) If Right(wsShName.Name, 7) < "Monthly" Then 'ignore these sheeets Select Case wsShName.Name Case "(Code Key)" 'Excluded sheets Exit Sub Case Else 'if password hasn't been entered yet, then ask for password If bPwrdEntrd = False Then Call PasswordEntry End If End Select End If End Sub Sub PasswordEntry() Dim rFoundShName As Range Dim rShNames As Range Dim wsPwrdNames As Worksheet 'Dim sPwrd As String Set wsPwrdNames = ThisWorkbook.Sheets("sheet1") Set rShNames = wsPwrdNames.Range("A1:A8") wsPwrdNames.Visible = True ufPwrdEntry.Show Set rFoundShName = rShNames.Find(ActiveSheet.Name, _ LookIn:=xlValues, _ LookAt:=xlWhole) If sPwrd = rFoundShName.Offset(1, 0).Value Then bPwrdEntrd = True End If wsPwrdNames.Visible = False End Sub And this code in the userform: Sub cbOK_Click() sPwrd = ufPwrdEntry.tbPwrdEntry.Text <---- OBJECT DOESN'T SUPPORT THIS.... If sPwrd = "" Then MsgBox "Please enter the password to access this worksheet.", vbOKCancel End If End Sub I tried changing the .text to .value, but that didn't help. Any ideas on why? Thanks! -- Dave Peterson- Hide quoted text - - Show quoted text - -- Dave Peterson- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
why do i "sometimes" need to "edit chart object" before "source da | Charts and Charting in Excel | |||
"Object doesn't support this property or method" in Excel 2003 | Excel Discussion (Misc queries) | |||
VBA error: "object doesn't support this property or method" | Excel Discussion (Misc queries) | |||
What is Error "Method "Paste" of object "_Worksheet" failed? | Excel Programming | |||
Help "438 - Object doesn't support this property or method" | Excel Programming |