Free Microsoft Excel 2013 Quick Reference

Return To Current/Active Cell After VBA Macro Code Has Run

Don't understand where I am going wrong with this.

I have built a report on one (sheet 1) sheet where some of the data feeds off another sheet (sheet 2).
I have made room to fit 1000 rows of data on the report and then sub-totals at the end of this, I am then hidding rows between the end of the data and subtotals so user does not need to scroll down.

What I would like to occur is that when a new record (row) is created in sheet 2, that the hidden row for that sheet is uncovered in sheet 1.

My code works till it gets to Cells(cellRw, cellCol).Select and then returns a "Run-time error '104': Select method of Range class failed


	VB:
	
     
    Dim top, bot, diff, cellCol, cellRw As Integer 
    Dim curSht As String 
     
    With Application 
        .ScreenUpdating = False 
        .Calculation = xlCalculationManual 
    End With 
     
     ' Get current sheet and cell location details
    curSht = ActiveSheet.Name 
    cellCol = ActiveCell.Column 
    cellRw = ActiveCell.Row 
     
    Sheets("Portfolio View").Select 
     ' define range of cells to hide and hide them
     
    diff = WorksheetFunction.CountA(Range("f:f")) - WorksheetFunction.CountIf(Range("f:f"), 0) 
    top = diff + 9 
    bot = 1000 
     
    Rows("20:1000").Select 
    Selection.EntireRow.Hidden = False 
    Rows(top & ":" & bot).Select 
    Selection.EntireRow.Hidden = True 
     
    Sheets(curSht).Select 
    Cells(cellRw, cellCol).Select 
     
    With Application 
        .ScreenUpdating = True 
        .Calculation = xlCalculationAutomatic 
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help or suggestions on how to do this better would be appreciated.

Your help would be greatley appreciated


Post your answer or comment

comments powered by Disqus
If I activate a cell with VBA macro code, if the cell is already visible on screen, the window doesn't scroll at all. If the cell isn't currently visible, Excel scrolls the window so that the selected cell's row is roughly in the middle of the window. Is there any way to scroll the window with VBA code so that the selected cell is near the top of the window. I want as much of the text below the selected cell to be visible on screen as possible.

TIA.

I am trying to display a user form after a cell value is entered to allow the user to select an option that writes a value to another cell. My issue is when this value is written I want to return to the original cell or to a cell below the cell that started the user from execution and make that the active cell. I have more code to write regarding my If, Then, Else but my attempt at offseting back to make a selection is failing. I started with this idea by making the assumption that some one would work down from the top of the sheet but this does not allow for some one comming back in the sheet and making changes mid stream. Any help is appreciated!


	VB:
	
 Range) 
    Set Target = Range("F:F") 
    Column.Show 
    If optPsell = True Then 
        ActiveCell.Offset(0, 7).Value = "P" 
    End If 
    If ActiveCell.Value = "P" Then 
        Select.("F65536").End(xlUp).Offset(1,0) 
         'MsgBox ("Please price your next repair or Exit", vbOKOnly)
         
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I need to name a cell that is the active cell. The active cell can be any cell that is selected at the time I need to name it. I can get it to name the cell using the cell address, but I don't know how to change it to the active cell.

Below is the code that I tried, but the syntax that I tried to say what cell is to be named simply isn't correct.

Need a little help please.

Hi all

I have the code below in Access which exports data into an existing Excel
spreadsheet. The cells are formatted as general number but the code changes
them to date format. Is there a way to specify the data type before the copy
or should I try and find some code to format the cells after the export code
has run?

Thanks in advance for any help.
Sue

Is there a formula that returns the column of the current active cell?


	VB:
	
=column(C5) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
will return the value 3

But what if I want that formula to always reference whatever the currently selected cell is?

=column(ActiveCell)

In Excel 2010 I have a multi page spreadsheet.

There is a table page with each row holding a record that needs to be issued a receipt on a portable printer. This page has a cell with a formula that keeps the coordinates of the current active cell on that page which is accessed from another page to pull the data off the current row in order to print a receipt formatted for 2 inch roll paper using the data from the referenced row.

This all works after much effort but when I went to protect the cell with the coordinates, which is vital to the proper functioning of the application, I discovered that protecting this cell locks the whole sheet.

It appears to me when this cell is protected whenever you try to select any cell, protected or not, excel thinks you are trying to access this protected 'coordinates' cell and returns a 1004 error from this piece of code.


	VB:
	
 Range) 
    Range("AA1").Value = ActiveCell.Address(0, 0) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I can leave the cell unprotected but I am reluctant to do that because the end users are not computer literate.

I have checked the protection status of all cells and all the data entry cells are unprotected correctly. Once I un-protect that one 'coordinates' cell I again have access to all the unprotected cells correctly.

I am guessing that having the code above un-protect that cell 'AA1' before updating the value and re-protecting it afterwards would do the trick. At the end of a long day and being excel illiterate I would appreciate the syntax to do this.

Hi,

I am looking for VBA that will add the value of the current active cell on the sheet to the value in cell F12. The maximum value of F12 cannot exceed 1000. So if the value in F12 = 950 and 100 is the value in the active cell the maximum value in F12 should show 1000, not 1050.

It should do this on the click of a button.

Thanks a lot,

-Upside

I am using Excel to tabulate scores for my employees. We work in a very busy and open office, so there is a need to be able to obfuscate the scores, but also help keep from losing my place while punching the scores.

I would like it to be able to return to the last cell that I was punching a score in...I used "ActiveCell.SpecialCells(xlLastCell).Select". I have also used " x= cells(Rows.count,2).end(xlUP).row" followed by "cells(x+1,2).select", but both with no luck...

Thanks,
David


	VB:
	
 Hide_Scores() 
    Range("B15:EU35").Select 
    ActiveSheet.Unprotect 
    With Selection 
        .HorizontalAlignment = xlCenter 
        .VerticalAlignment = xlCenter 
        .WrapText = False 
        .Orientation = 0 
        .AddIndent = False 
        .IndentLevel = 0 
        .ShrinkToFit = False 
        .ReadingOrder = xlContext 
        .MergeCells = False 
    End With 
    With Selection.Font 
        .Name = "Arial" 
        .FontStyle = "Bold" 
        .Size = 10 
        .Strikethrough = False 
        .Superscript = False 
        .Subscript = False 
        .OutlineFont = False 
        .Shadow = False 
        .Underline = xlUnderlineStyleNone 
        .ColorIndex = 2 
    End With 
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True 
    x = ActiveSheet.UsedRange.Rows.Count 
    ActiveCell.SpecialCells(xlLastCell).Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am creating a time clock spreadsheet where my employees can push a macro button that inputs their clock in time, and one for the clock out time. What I want is to protect that cell after they ran the macro so they can't go in and change the time. So if they are suppose to be there at 8am, but the macro clocked in at 8:05 - I don't want them to be able to click in the cell and enter 8:00am. Does this make sense?

My macro looks like this so far:

Sub Clock_In()
'
' Clock_In Macro
' Inserts clock-in time for both morning and lunch return.
'
'
ActiveCell.Value = Time
ActiveCell.NumberFormat = "h:mm AM/PM"

End Sub

Can someone help me with the rest of the language to protect that cell? It has to be where it can protect any cell that macro is ran on. Thanks!
~Nina

The following is some code that I have written in Excel 2000 using
Windows 98. It works fine except that it does not return to the
original active sheet and cell from before the routine was called, and
screen updating does not seem to be turning off.

Any ideas? Thanks!

Private Sub TextBox1_Change()

' This macro is called when the value in TextBox1 is changed.
' Textbox1 is a textbox in sheet 2 which is linked to a cell
' in sheet 2 whose value changes as certain cell values
' in sheet1 are changed by the user.

Dim OriginalCell As Range
Dim OriginalSheet As Worksheet

' Record the original active sheet and cell from before the macro
' was started so as to be able to return there when the macro
' has finished.

Set OriginalCell = ActiveCell
Set OriginalSheet = ActiveSheet

Application.ScreenUpdating = False

If Sheet2.Range("q10").Value > 1 / 6 _
And (Sheet2.Range("q8").Value < 0.25 _
Or Sheet2.Range("q9").Value < 0.25) _
And Sheet2.Range("q8").Value > 0 _
And Sheet2.Range("q9").Value > 0 Then

' Call the subroutine which evaluates soil bearing pressures.

ResolveSoilPressures

' If the solver did not find valid results, let the user
' know about it.

If Abs(1 - Sheet2.Range("H16").Value / _
Sheet2.Range("K5").Value) > 0.00001 _
Or Abs(1 - Sheet2.Range("h17").Value / _
Sheet2.Range("K6").Value) > 0.00001 Then
MsgBox ("The solver failed to find an exact solution for _
this footing." & Chr$(13) & " Please change _
footing parameters and rerun design.")

End If

' Go back to the original active sheet and cell from before the
' macro was called.

OriginalSheet.Activate
OriginalCell.Select

Application.ScreenUpdating = True

End Sub

Public Sub ResolveSoilPressures()

SolverReset
SolverLoad LoadArea:="Sheet2!$A$1:$A$9"
SolverOptions MaxTime:=100, Iterations:=100, _
Precision:=0.0000000001, AssumeLinear:=False, _
StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1,
_
IntTolerance:=5, Scaling:=False, Convergence:=0.0001, _
AssumeNonNeg:=False
SolverOk SetCell:="Sheet2!$H$19", MaxMinVal:=1, ValueOf:="0", _
ByChange:= "Sheet2!$H$5,Sheet2!$H$6,Sheet2!$H$11,Sheet2!$H$12 "
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1

End Sub

If sheet 1 is the active sheet when the textbox_change event is called,
then the routine fails at "OriginalCell.Select" with the error, "Select
method of Range class failed". If sheet 2 is the active sheet when the
textbox_change event is called, it works fine returning the pointer to
the original cell from before the routine was called.

Also, "Application.ScreenUpdating = False" does not seem to work. The
screen still blinks and blips as the calculations are run. I would
like solver to run without the user seeing the shift from screen 1 to
screen 2 and back again.

Thank you for any help.
Giz

The following is some code that I have written in Excel 2000 using
Windows 98. It works fine except that it does not return to the
original active sheet and cell from before the routine was called. Any
ideas? Thanks!

Private Sub TextBox1_Change()

' This macro is called when the value in TextBox1 is changed.
' Textbox1 is a textbox in sheet 2 which is linked to a cell
' in sheet 2 whose value changes as certain cell values
' in sheet1 are changed by the user.

Dim OriginalCell As Range
Dim OriginalSheet As Worksheet

' Record the original active sheet and cell from before the macro
' was started so as to be able to return there when the macro
' has finished.

Set OriginalCell = ActiveCell
Set OriginalSheet = ActiveSheet

If Sheet2.Range("q10").Value > 1 / 6 _
And (Sheet2.Range("q8").Value < 0.25 _
Or Sheet2.Range("q9").Value < 0.25) _
And Sheet2.Range("q8").Value > 0 _
And Sheet2.Range("q9").Value > 0 Then

' Call the subroutine which evaluates soil bearing pressures.

ResolveSoilPressures

' If the solver did not find valid results, let the user
' know about it.

If Abs(1 - Sheet2.Range("H16").Value / _
Sheet2.Range("K5").Value) > 0.00001 _
Or Abs(1 - Sheet2.Range("h17").Value / _
Sheet2.Range("K6").Value) > 0.00001 Then
MsgBox ("The solver failed to find an exact solution for" _
& Chr$(13) & _
"this footing. Please review to confirm the
results" _
& Chr$(13) & _
"of this design. If the results are invalid," _
& Chr$(13) & _
"please change footing parameters and rerun
design.")

End If

' Go back to the original active sheet and cell from before the
macro was called.

OriginalSheet.Activate
OriginalCell.Select

End Sub

Public Sub ResolveSoilPressures()

SolverReset
SolverLoad LoadArea:="Sheet2!$A$1:$A$9"
SolverOptions MaxTime:=100, Iterations:=100,
Precision:=0.0000000001, _
AssumeLinear:=False, StepThru:=False, Estimates:=1,
Derivatives:=1, _
SearchOption:=1, IntTolerance:=5, Scaling:=False,
Convergence:=0.0001, _
AssumeNonNeg:=False
SolverOk SetCell:="Sheet2!$H$19", MaxMinVal:=1, ValueOf:="0",
ByChange:= _
"Sheet2!$H$5,Sheet2!$H$6,Sheet2!$H$11,Sheet2!$H$12 "
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1

End Sub

If sheet 1 is the active sheet when the textbox_change event is called,
then the routine fails at "OriginalCell.Select" with the error, "Select
method of Range class failed". If sheet 2 is the active sheet when the
textbox_change event is called, it works fine returning the pointer to
the original cell from before the routine was called.

Thank you for any help.
Giz

The following is some code that I have written in Excel 2000 using
Windows 98. It works fine except that it does not return to the
original active sheet and cell from before the routine was called. Any
ideas? Thanks!

Private Sub TextBox1_Change()

' This macro is called when the value in TextBox1 is changed.
' Textbox1 is a textbox in sheet 2 which is linked to a cell
' in sheet 2 whose value changes as certain cell values
' in sheet1 are changed by the user.

Dim OriginalCell As Range
Dim OriginalSheet As Worksheet

' Record the original active sheet and cell from before the macro
' was started so as to be able to return there when the macro
' has finished.

Set OriginalCell = ActiveCell
Set OriginalSheet = ActiveSheet

If Sheet2.Range("q10").Value > 1 / 6 _
And (Sheet2.Range("q8").Value < 0.25 _
Or Sheet2.Range("q9").Value < 0.25) _
And Sheet2.Range("q8").Value > 0 _
And Sheet2.Range("q9").Value > 0 Then

' Call the subroutine which evaluates soil bearing pressures.

ResolveSoilPressures

' If the solver did not find valid results, let the user
' know about it.

If Abs(1 - Sheet2.Range("H16").Value / _
Sheet2.Range("K5").Value) > 0.00001 _
Or Abs(1 - Sheet2.Range("h17").Value / _
Sheet2.Range("K6").Value) > 0.00001 Then
MsgBox ("The solver failed to find an exact solution for" _
& Chr$(13) & _
"this footing. Please review to confirm the
results" _
& Chr$(13) & _
"of this design. If the results are invalid," _
& Chr$(13) & _
"please change footing parameters and rerun
design.")

End If

' Go back to the original active sheet and cell from before the
macro was called.

OriginalSheet.Activate
OriginalCell.Select

End Sub

Public Sub ResolveSoilPressures()

SolverReset
SolverLoad LoadArea:="Sheet2!$A$1:$A$9"
SolverOptions MaxTime:=100, Iterations:=100,
Precision:=0.0000000001, _
AssumeLinear:=False, StepThru:=False, Estimates:=1,
Derivatives:=1, _
SearchOption:=1, IntTolerance:=5, Scaling:=False,
Convergence:=0.0001, _
AssumeNonNeg:=False
SolverOk SetCell:="Sheet2!$H$19", MaxMinVal:=1, ValueOf:="0",
ByChange:= _
"Sheet2!$H$5,Sheet2!$H$6,Sheet2!$H$11,Sheet2!$H$12"
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1

End Sub

If sheet 1 is the active sheet when the textbox_change event is called,
then the routine fails at "OriginalCell.Select" with the error, "Select
method of Range class failed". If sheet 2 is the active sheet when the
textbox_change event is called, it works fine returning the pointer to
the original cell from before the routine was called.

Thank you for any help.
Giz

First of all hey all

Im currently developing a Gantt chart for work where you need to be able to alter the timeframe in accodance to the week numbers in a given month.

Originally I was thinking of making a big deal out of it but I´ve ended up reaching for a worksheet where you have 3 buttons (4 weeks), (5 weeks), (6 Weeks) and by pressing these you would be able to copy a frame (hidden somewhere else in the sheet) and thereby make your own gantt chart.

I have recorded a macro that copy and paste´s the 4 week module, but I just can´t get it to be placed where the original active cell were. Are there any specific way to remember what cell was activated before you run a macro ?

Were talking excel 2003 btw.

Regards

-Thomas

The following is some code that I have written in Excel 2000 using
Windows 98. It works fine except that it does not return to the
original active sheet and cell from before the routine was called, and
screen updating does not seem to be turning off.

Any ideas? Thanks!

Private Sub TextBox1_Change()

' This macro is called when the value in TextBox1 is changed.
' Textbox1 is a textbox in sheet 2 which is linked to a cell
' in sheet 2 whose value changes as certain cell values
' in sheet1 are changed by the user.

Dim OriginalCell As Range
Dim OriginalSheet As Worksheet

' Record the original active sheet and cell from before the macro
' was started so as to be able to return there when the macro
' has finished.

Set OriginalCell = ActiveCell
Set OriginalSheet = ActiveSheet

Application.ScreenUpdating = False

If Sheet2.Range("q10").Value > 1 / 6 _
And (Sheet2.Range("q8").Value < 0.25 _
Or Sheet2.Range("q9").Value < 0.25) _
And Sheet2.Range("q8").Value > 0 _
And Sheet2.Range("q9").Value > 0 Then

' Call the subroutine which evaluates soil bearing pressures.

ResolveSoilPressures

' If the solver did not find valid results, let the user
' know about it.

If Abs(1 - Sheet2.Range("H16").Value / _
Sheet2.Range("K5").Value) > 0.00001 _
Or Abs(1 - Sheet2.Range("h17").Value / _
Sheet2.Range("K6").Value) > 0.00001 Then
MsgBox ("The solver failed to find an exact solution for _
this footing." & Chr$(13) & " Please change _
footing parameters and rerun design.")

End If

' Go back to the original active sheet and cell from before the
' macro was called.

OriginalSheet.Activate
OriginalCell.Select

Application.ScreenUpdating = True

End Sub

Public Sub ResolveSoilPressures()

SolverReset
SolverLoad LoadArea:="Sheet2!$A$1:$A$9"
SolverOptions MaxTime:=100, Iterations:=100, _
Precision:=0.0000000001, AssumeLinear:=False, _
StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1,
_
IntTolerance:=5, Scaling:=False, Convergence:=0.0001, _
AssumeNonNeg:=False
SolverOk SetCell:="Sheet2!$H$19", MaxMinVal:=1, ValueOf:="0", _
ByChange:= "Sheet2!$H$5,Sheet2!$H$6,Sheet2!$H$11,Sheet2!$H$12"
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1

End Sub

If sheet 1 is the active sheet when the textbox_change event is called,
then the routine fails at "OriginalCell.Select" with the error, "Select
method of Range class failed". If sheet 2 is the active sheet when the
textbox_change event is called, it works fine returning the pointer to
the original cell from before the routine was called.

Also, "Application.ScreenUpdating = False" does not seem to work. The
screen still blinks and blips as the calculations are run. I would
like solver to run without the user seeing the shift from screen 1 to
screen 2 and back again.

Thank you for any help.
Giz

if, for example, I want to set a value of a variable in a macro code (variable called Index, containing a string "AB") to cell "A7" in "worksheet 1", how do I do that?

I tried doing the following:


	VB:
	
Worksheets ("worksheet 1").Range("A7").Value =  Index 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I was expecting that, when returning to excel worksheet, I'd find the value in the variable Index (="AB") placed in cell "A7"

but it didn't work, and instead gave me the following error:
"object doesn't support this property or method"

any ideas?

Hi everyone

I am trying to write some code that will compare a value A1 with the whole of column B and if the contents of the cell A1 can be found in column B then both cells should be deleted. The code will then loop to cell A2 and repeat

I am happy finding and deleting the value matched in column B, but do not know how I can return to the previously active cell (i.e. A1, and later A2 and so on) so that cell can be deleted and the code can continue.

Does anyone have any ideas on how this could be done?

Want to Copy the COntents of a cell and not the cell with VBA Macro

I am looking to keep the Contents of a cell ready to be pasted in an email textbox.
I have a bunch of emails appended in a cell with the help of a macro however going one step further I would like the contents of the cell and not the cell to be stored in the Copy ClipBoard and when I put my cursor in the To,CC,BCC text boxes then I can simply CONTRL+V or Right Click Paste

Warm Regards
e4excel

Total noob here, please be gentle.

Is it possible to set a new active cell from the value of the current active cell? I am kicking myself because I am certain I have done this before, but have been researching for hours with no luck.

The active cell is "G22". The cell address in this cell is a concatenated value dependent on the values of two text boxes on a user form. In this case the concatenated resultant is “P49”.

Stumped and feeling foolish. Any help appreciated.

I have a column with entrys of 2 kinds. Some being Hyperlinks and others with normal type data. All cells are locked with password except for cells that will possibly have entrys made in them.

Is there a way to move the active cell to R1C1 after any hyperlink clicked? R1C1 is unlocked.

I have finally gotten this thing coded and Ive reached a snag. The code is looping and calling macros depending on values, but I need it to loop on the ending active cell of the macro it runs, and not the next cell down the A column.

Sub SelectCase()
Dim Last As Long
Dim Rng As Range
Last = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range("A1:A487")
For Each cell In Rng
Select Case cell.Value

Case 2
Call macro2
Case 3
Call macro3
ETC.....

End Select
Next Cell...........----> I need this to select the Case cell.value on the ending
End Sub.....................active cell of the macro that was
................................run (macro2, macro3, etc)

Any ideas on how to do this?

What would be a simple code to select a cell:

- directly below the currently active cell?
- directly to the right of the currently active cell?

Thank you.

Hi,
I have embedded feeds from an outside source feeding into my spreadsheets. I have a problem in that whem i am swiching between worksheets, on my return it will always go back to the cells where the embeded feeds are coming in rather than to the last cell/ "view" viewed or even the active cell. Is there any way i can give precedence to the active cell as at the minute i am constantly recrolling to my intended position on a worksheet once i leave it to view another sheet.
thanks Folks
James

Hi, I'm very new to VBA programming. I am trying to create a booking system.

The system is like this:

I have calenders by month on each sheet. On each sheet, there are cells representing a date. When the user click the save button, a message box prompting the name of the person who save the value and the purpose of booking. Is it possible to have 2 static data saved to an active cell? I cant find any information on this. Will appreciate any advice/alternative solutions to this. Thanks in advance!

Hi, I found only one other thread on this issue and I was unsucessful in duplicating the results. I believe it may be because the other person was using 2003 and I'm still stuck with 97.

I have a macro attached to a button that will place a value at the end of a row. Once this is done I make the cell to the immediate right of the button the active cell. My problem is many times I have user's either accidentally or from forgetfullness or from working with a bad mouse end up double clicking or clicking twice on the button. This adds an extra unwanted value that messes up further calculations. Is it possible to have the mouse pointer move to the active cell after the macro has run.

Alternatively is there any other way to prevent "accidental" clicking of the button twice?

Thanks

Andrea


No luck finding an answer? You could always try Google.