UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Compile Error In Vba - In Valid Use Of Me Keyword, Any Version    
 
   
bakersburg9
post Aug 7 2019, 09:52 AM
Post#1



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


Can't understand why I'm getting this message - I want to rename the worksheets with the value in A1 - actually, I don't really need it to do all the worksheets, like my code is written for - I just really need it to do the CURRENT worksheet - but I'm just testing it out...

CODE
Sub goToNextWorksheetTEST()
    
Dim WsheetTEST As Worksheet
         On Error Resume Next
             For Each WsheetTEST In Me.Worksheets
             WsheetTEST.Name = WsheetTEST.Range("A1")
             Next WsheetTEST
         On Error GoTo 0
        
  End Sub
Go to the top of the page
 
theDBguy
post Aug 7 2019, 10:13 AM
Post#2


Access Wiki and Forums Moderator
Posts: 76,376
Joined: 19-June 07
From: SunnySandyEggo


Hi. I don't code in Excel but does it use This in place of Me? Just wondering...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
DanielPineault
post Aug 7 2019, 11:13 AM
Post#3


UtterAccess VIP
Posts: 6,899
Joined: 30-June 11



Me. is an Access thing.

For Excel you would use Application.Worksheets
CODE
Sub goToNextWorksheetTEST()
    Dim WsheetTEST            As Worksheet

    On Error Resume Next
    For Each WsheetTEST In Application.Worksheets
        WsheetTEST.Name = WsheetTEST.Range("A1")
    Next WsheetTEST
    On Error GoTo 0
End Sub

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
cheekybuddha
post Aug 7 2019, 11:23 AM
Post#4


UtterAccess VIP
Posts: 11,514
Joined: 6-December 03
From: Telegraph Hill


You can use Me in Excel if the function/sub is declared in the ThisWorkbook module.

Otherwise you will have to set an explicit reference to the workbook/worksheet or use something like the hideous ActiveWorkbook/ActiveSheet.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
bakersburg9
post Aug 7 2019, 12:38 PM
Post#5



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


Thanks, guys! How would I edit it to just run for the active workbook, then go to the next workbook ? I know that at the end of the day, that's what it happens to be doing, but I want this to run at the end of a block of code - then perform the same process, and THENdo the saving with the worksheet name as the value in cell A1

Thanks so much !!! cool.gif

Steve
Go to the top of the page
 
ipisors12
post Aug 7 2019, 01:19 PM
Post#6



Posts: 9
Joined: 14-March 19



The proper way to code this is neither Me nor Application ... but rather, ThisWorkbook.Worksheets (or x.Worksheets, where x is the name of a propertly typed and set workbook variable).
Go to the top of the page
 
DanielPineault
post Aug 7 2019, 01:36 PM
Post#7


UtterAccess VIP
Posts: 6,899
Joined: 30-June 11



ipisors12 is entirely right (ThisWorkbook.Worksheets). 2 weeks off and I'm already rusty. crazy.gif

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
cheekybuddha
post Aug 7 2019, 04:29 PM
Post#8


UtterAccess VIP
Posts: 11,514
Joined: 6-December 03
From: Telegraph Hill


However, when you are within the ThisWorkbook module you can use Me, just as when you are in Access you do not refer to Forms.myForm when you are within myForm's module. shrug.gif

@Isaac - long time no see! wavehi.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
ipisors12
post Aug 7 2019, 06:02 PM
Post#9



Posts: 9
Joined: 14-March 19



David - indeed! Good to see you hope all is well thumbup.gif
Go to the top of the page
 
Debaser
post Aug 8 2019, 07:01 AM
Post#10



Posts: 147
Joined: 11-October 18



Not just ThisWorkbook - it works in any class module to refer to the current instance of that class.
Go to the top of the page
 
cheekybuddha
post Aug 8 2019, 07:48 AM
Post#11


UtterAccess VIP
Posts: 11,514
Joined: 6-December 03
From: Telegraph Hill


>> it works in any class module to refer to the current instance of that class. <<

Very true - great point! thumbup.gif

Yet in the context of the code in the original post, it will either be ThisWorkbook or a class that exposes a Worksheets property returning a Collection of Worksheets or objects with some similar properties! grin.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
Debaser
post Aug 9 2019, 07:42 AM
Post#12



Posts: 147
Joined: 11-October 18



Yep, totally agree. smile.gif
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th October 2019 - 01:15 PM