Why does my VBA macro generate an error on a Select command ?

Hi Experts,

I'm developing a small Excel application which prompts the user to enter name, organisation and email address and then populates cells with these values on a sheet.

However, after the input, I'm getting a 'Run time error 1004 Select method of Range class failed' when I select the sheet to display.

I've attached the application.

Any ideas anyone ?

Thanks
Toco
TNA-Test-3.xlsm
TocogroupAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

andrewssd3Commented:
You just need to activate the sheet before you can select:
Public Sub Retrieve_Details()

   Dim wsUsingExcel As Worksheet
   Dim NameInput As String
   Dim CompanyInput As String
   Dim EmailInput As String

   Set wsUsingExcel = Sheets("Using_Excel")

' If name doesn't exist in 'Using Excel' name field then prompt for input and populate sheet
   If wsUsingExcel.Range("D6") = "" Then
      ' Prompt the delegate for name
      NameInput = InputBox("TOCO TRAINING - Delegate name" & vbNewLine & vbNewLine & "Please enter your name")
      wsUsingExcel.Range("D6") = NameInput
      If NameInput = "" Then
         Exit Sub
      End If
   End If
   
' If company name doesn't exist in 'Using Excel' organisation field then prompt for input and populate sheet
   If wsUsingExcel.Range("D8") = "" Then
      ' Prompt the delegate for organisation
      CompanyInput = InputBox("TOCO TRAINING - Organisation" & vbNewLine & vbNewLine & "Please enter the name of your organisation or company")
      wsUsingExcel.Range("D8") = CompanyInput
      If CompanyInput = "" Then
         Exit Sub
      End If
   End If
      
' If email address doesn't exist in 'Using Excel' email field then prompt for input and populate sheet
   If wsUsingExcel.Range("D10") = "" Then
      ' Prompt the delegate for email address
      EmailInput = InputBox("TOCO TRAINING - Email address" & vbNewLine & vbNewLine & "Please enter your email address")
      wsUsingExcel.Range("D10") = EmailInput
      If EmailInput = "" Then
         Exit Sub
      End If
   End If
       
   wsUsingExcel.Activate
   wsUsingExcel.Range("D10").Select

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gowflowCommented:
Will this do it for you ?
gowflow
TNA-Test-3.xlsm
0
gowflowCommented:
Sorry for my input I didn't see your reply we crossed by 3 minutes.
gowflow
0
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

TocogroupAuthor Commented:
Thanks both of you for your responses. It's such a basic error of mine......you can tell I'm still a rookie.

Not sure what I do here. I've been criticised before for not sharing the points.

What do you suggest ?
0
gowflowCommented:
no problem for me you can allocate to Andrew all of it my pleasure to help. not here for points
gowflow
0
TocogroupAuthor Commented:
Many thanks gowflow. Much appreciated.
0
TocogroupAuthor Commented:
Thanks for the help
Toco
0
andrewssd3Commented:
Thanks gowflow.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.