EXCEL VBA Select Cell in different Named Range

  Heefie 19:43 13 Sep 2005
Locked

Any help appreciated, this is so frustrating (and probably simple !!) ... In my code :

Private Sub AddNewEmployee()


Dim FirstBlank As Range


Dim EmpNam As Range


With Range("EmployeeNamesCol1")


Set EmpNam = .Find(What:=EmployeeName)


End With


If Not EmpNam Is Nothing Then


MyError = "Yes"


MsgBox prompt:="Employee Already Exists !!!", Title:="Program Error " & "04" & " ...", Buttons:=vbCritical


Else


With Range("EmployeeNamesCol1")


Set FirstBlank = .Find(What:="")


End With


If FirstBlank Is Nothing Then


MyError = "Yes"


MsgBox prompt:="Employee Range is full !!!", Title:="Program Error " & "05" & " ...", Buttons:=vbCritical


Else


FirstBlank = EmployeeName


Range("FirstBlank").Offset(0, 1).Activate


End If


End If


End Sub


I am getting an error in the Offset line ... basically, I've searched for and found that a user entered Employee name doesn't exist, so have entered it into the first blank cell in the named Range. Now I want the Active Cell to be the Cell immediately to the right ... any ideas ?

P.S Fingers crossed for the formatting !!!!

  VoG II 19:53 13 Sep 2005

FirstBlank.Offset(0, 1).Activate

  Heefie 22:01 13 Sep 2005

I get the error message "Run-time error '1004': Activate Method of Range class failed" ...

  VoG II 22:08 13 Sep 2005

It works for me.

Option Explicit

Sub AddNewEmployee()

Dim FirstBlank As Range

Dim EmpNam As Range

Dim EmployeeName As String, MyError As String

EmployeeName = "Peter"

With Range("EmployeeNamesCol1")

Set EmpNam = .Find(What:=EmployeeName)

End With

If Not EmpNam Is Nothing Then

MyError = "Yes"

MsgBox prompt:="Employee Already Exists !!!", Title:="Program Error " & "04" & " ...", Buttons:=vbCritical

Else

With Range("EmployeeNamesCol1")

Set FirstBlank = .Find(What:="")

End With

If FirstBlank Is Nothing Then

MyError = "Yes"

MsgBox prompt:="Employee Range is full !!!", Title:="Program Error " & "05" & " ...", Buttons:=vbCritical

Else

FirstBlank = EmployeeName

'Range("FirstBlank").Offset(0, 1).Activate

FirstBlank.Offset(0, 1).Activate

End If

End If

End Sub

=====================================

The Range in the Offset statement is unnecessary as FirstBlank is already DIMd as a Range.

  Heefie 22:09 13 Sep 2005

... it may be something to do with this sheet being hidden. If this is the case, should I unhide the sheet, make the Activation & then re-hide the sjeet, or is there another way of making the system use the Cell I want, without Activating it ?!?

Basically, I will want to move a value into it in the next Routine, it doesn't have to be the ActiveCell, I just assumed this was the best/easiest way to access it ...

  VoG II 22:19 13 Sep 2005

I don't think that you can activate a cell in a hidden sheet.

Which begs the question, why would you want to do this? I mean activate a cell in a hidden sheet.

  Heefie 22:21 13 Sep 2005

I've unhidden & re-hidden the Worksheet & it makes no difference !!!

  VoG II 22:25 13 Sep 2005

You can directly address the cell in any sheet

If you like, click my yellow envelope and I'll send you an example that works.

  Heefie 22:35 13 Sep 2005

It's a user spreadsheet with all sheets hidden except a menu sheet, which contains buttons allowing them to od whatever they want, add an employee, update a daily rate, start a new month, etc., etc.

The Employee sheet has a list of Employees, and each one has a rate of 1-9.

The Rate sheet has 9 entries and a value for each level.

These are hidden because I don't want the users going into the individual sheets and updating by Cell, I want the whole spreadsheet run from the menu screen, under my control and under my rules (not that I don't trust them or anything <s> !!!) ...

In this situation, the user has entered a new Employee name, the code has validated that the name does not exist, so then finds the first blank cell in the Employee name list, for example A13, and the name is entered here ... he is then directed to the Rates selection where he is shown a list of the valid rates and he selects one, say £400, which is in Rates!B6 ... Rates!A6 will contain the number 6 and this is what I want moved into the Employee sheet B13 ... does that make sense ?!?!

  VoG II 22:39 13 Sep 2005

Yes. Please click envelope to receive my mock-up or to send me yours.

  Heefie 22:43 13 Sep 2005

... but it keeps timing out !!!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

AMD Radeon Adrenalin release date, new features, compatible graphics cards

Inside the iMac Pro - Apple's most powerful Mac yet

iMac Pro release date, UK price & specs

Comment nettoyer Windows et optimiser son PC gratuitement ?