How do you pause a Macro to move the cursor from the cell it is on to another cell

Trebor J · Nov 9, 2022

How do you pause a Macro and be allowed to move the cursor from the cell it is on to another cell?

I have written a macro that copies in a template consisting of 2 formulas and 2 input cells, all on the same row.

After the template is copied in, the cursor moves to the first input cell.

After data is entered and “Enter” pressed, the cursor moves to the second input cell.

After data is entered and “Enter” pressed, the cursor moves down one row and left 3 columns to sit in the cell just below the start of where the template was entered.

A message box then asks if you wish to “Move to Approximate Next Period End”.

If you click on Yes, the cursor then moves down 29 Rows.

All well and good except you might not be in the cell that is in the row that has a cell with the date you want in it. You need to move the cursor up or down a row or two.

I found some coding on the internet that purports to pause the Macro and allows one to move the cursor and it appears to do that.
I say “appears to do that” because a Message Box appears giving one the option to move the cursor, from the FROM CELL to the TO CELL, with the FROM CELL having a “Solid” border around it and the TO Cell having a “Dashed” border around it, if a new cell is chosen. The Message Box will then show the absolute address of the TO CELL.

If you then click on OK a new Message Box appears telling you that the Macro is “Working with the TO CELL’s address”. The TO Cell no longer has a “dashed” border. The FROM Cell still has a “Solid” border.

If you then click on OK to go ahead and then Yes to copy in the template, the template is copied into the FROM CELL. Not what I want !@%&). It should be copied into the TO CELL.

Any help or suggestion would be very much appreciated. Thanks guys and girls.

VBA Code:

Sub CopyInPeriodTotalsFormulasTesting()

Dim MykWh1 As Variant
Dim MykWh2 As Variant

Dim vbResult As VbMsgBoxResult

  Do Until IsEmpty(ActiveCell.Offset(0, -1))

  vbResult = MsgBox("    ***    COPY IN PERIOD TOTALS FORMULAS    ***     " & Chr(10) & Chr(10) & "  CHECK TO SEE CURSOR IS IN THE CORRECT CELL      ", vbYesNo, "CONTINUE Y/N?")

    If vbResult = vbYes _
      Then
        ActiveCell.Offset(0, 0).Select
          Range("PeriodTotalsFormulas").Copy Destination:=ActiveCell.Offset(0, 0)      '   Copy in the PeriodTotalFormula into the current cell
           
            ActiveCell.Offset(0, 2).Select
              MykWh1 = InputBox("Enter the kWh for 49")
               ActiveCell.Value = MykWh1
               
            ActiveCell.Offset(0, 1).Select
              MykWh2 = InputBox("Enter the kWh for 64")
                ActiveCell.Value = MykWh2
               
        ActiveCell.Offset(1, -3).Select
       
        vbResult = MsgBox("  Move to Approx Next Period End   ", vbYesNo, "CONTINUE Y/N?")
       
        If vbResult = vbYes _
          Then
            ActiveCell.Offset(29, 0).Select                  'Move to next approximate cell below

            Dim Rng As Range
              On Error Resume Next
                Set Rng = Application.InputBox("Enter to Continue " & _
                        "or Select a cell with the Mouse", _
                        "Cell entry", ActiveCell.Address, , , , , 8)
                If Rng Is Nothing Then
                    MsgBox "You cancelled!"
                Else
                  MsgBox "Working with cell " & Rng(1).Address

                End If
         
          Else
            Exit Do
        End If
           
      Else
        Exit Do
     
    End If

Loop
 
End Sub

MovingTheCursorTesting 20221109 1852.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

1

2

Data for Moving the Cursor Testing

3

4

DATE

Daily kW hrs

Period Total kW hrs

Invoiced kWh

5

Total

u49

u64

6

7

8

16-Nov-15

28.90

-

Period Total Formula

9

17-Nov-15

17.70

10

18-Nov-15

22.84

11

19-Nov-15

26.29

12

20-Nov-15

23.43

13

21-Nov-15

22.63

14

22-Nov-15

14.17

15

23-Nov-15

21.65

16

24-Nov-15

12.54

17

25-Nov-15

11.35

18

26-Nov-15

34.90

19

27-Nov-15

46.42

20

28-Nov-15

51.17

21

29-Nov-15

50.52

22

30-Nov-15

38.09

23

01-Dec-15

29.71

24

02-Dec-15

35.24

25

03-Dec-15

44.87

26

04-Dec-15

43.85

27

05-Dec-15

40.08

28

06-Dec-15

39.68

29

07-Dec-15

48.57

30

08-Dec-15

39.57

31

09-Dec-15

38.32

32

10-Dec-15

34.87

33

11-Dec-15

28.82

34

12-Dec-15

29.97

35

13-Dec-15

32.39

36

14-Dec-15

41.39

37

15-Dec-15

42.27

38

16-Dec-15

32.11

Start

Start the macro from cell D38

39

17-Dec-15

39.25

Note the Months vary from 31, 30 & 28 days

40

18-Dec-15

48.59

and the invoice is not always the same day of the month

41

19-Dec-15

44.95

hence need for ability to move the cursor.

42

20-Dec-15

41.10

43

21-Dec-15

32.45

44

22-Dec-15

36.08

45

23-Dec-15

42.15

The Testing Macro will copy in the Template from cells D8:G8, and then take one to the Input Cells. Just enter any number into each of those 2 cells, and then follow the Messages to go through the proceedure. When requested move the cursor to the next 16th of the month cell.

46

24-Dec-15

40.62

47

25-Dec-15

33.86

48

26-Dec-15

26.67

49

27-Dec-15

29.36

50

28-Dec-15

31.38

51

29-Dec-15

28.15

52

30-Dec-15

29.76

53

31-Dec-15

34.27

54

01-Jan-16

24.19

55

02-Jan-16

27.83

56

03-Jan-16

25.08

57

04-Jan-16

49.32

58

05-Jan-16

41.48

59

06-Jan-16

34.25

60

07-Jan-16

28.42

61

08-Jan-16

28.43

62

09-Jan-16

34.72

63

10-Jan-16

21.53

64

11-Jan-16

33.26

65

12-Jan-16

30.88

66

13-Jan-16

32.73

67

14-Jan-16

34.51

68

15-Jan-16

32.07

69

16-Jan-16

30.02

70

17-Jan-16

33.26

71

18-Jan-16

24.31

72

19-Jan-16

20.14

73

20-Jan-16

19.52

74

21-Jan-16

19.13

75

22-Jan-16

22.89

76

23-Jan-16

27.83

77

24-Jan-16

29.13

78

25-Jan-16

25.72

79

26-Jan-16

32.10

80

27-Jan-16

28.36

81

28-Jan-16

23.22

82

29-Jan-16

32.54

83

30-Jan-16

26.29

84

31-Jan-16

27.35

85

01-Feb-16

24.21

86

02-Feb-16

29.18

87

03-Feb-16

27.22

88

04-Feb-16

30.62

89

05-Feb-16

32.56

90

06-Feb-16

29.34

91

07-Feb-16

25.93

92

08-Feb-16

30.58

93

09-Feb-16

29.40

94

10-Feb-16

25.25

95

11-Feb-16

30.79

96

12-Feb-16

27.29

97

13-Feb-16

26.08

98

14-Feb-16

27.79

99

15-Feb-16

28.23

100

16-Feb-16

25.79

17-Feb-16

28.12

102

18-Feb-16

27.50

103

19-Feb-16

22.92

104

20-Feb-16

28.76

105

21-Feb-16

27.85

106

22-Feb-16

25.28

107

23-Feb-16

28.63

108

24-Feb-16

26.60

109

25-Feb-16

33.88

110

26-Feb-16

27.21

111

27-Feb-16

25.48

112

28-Feb-16

27.48

113

29-Feb-16

29.00

114

01-Mar-16

24.01

115

02-Mar-16

29.31

116

03-Mar-16

27.85

117

04-Mar-16

23.21

118

05-Mar-16

22.86

119

06-Mar-16

27.91

120

07-Mar-16

27.11

121

08-Mar-16

30.77

122

09-Mar-16

28.07

123

10-Mar-16

24.27

124

11-Mar-16

25.00

125

12-Mar-16

30.45

126

13-Mar-16

26.99

127

14-Mar-16

32.15

128

15-Mar-16

28.89

129

16-Mar-16

27.65

17-Mar-16

29.04

131

18-Mar-16

28.74

132

19-Mar-16

33.60

133

20-Mar-16

29.67

134

21-Mar-16

26.62

135

22-Mar-16

32.27

136

23-Mar-16

27.61

137

24-Mar-16

26.75

138

25-Mar-16

26.52

139

26-Mar-16

31.65

140

27-Mar-16

23.74

141

28-Mar-16

29.15

142

29-Mar-16

28.04

143

30-Mar-16

28.29

144

31-Mar-16

25.82

145

01-Apr-16

27.32

146

02-Apr-16

28.14

147

03-Apr-16

30.21

148

04-Apr-16

28.20

149

05-Apr-16

33.65

150

06-Apr-16

31.01

151

07-Apr-16

26.81

152

08-Apr-16

27.10

153

09-Apr-16

31.83

154

10-Apr-16

25.37

155

11-Apr-16

27.50

156

12-Apr-16

33.30

157

13-Apr-16

34.12

158

14-Apr-16

27.30

159

15-Apr-16

23.56

160

16-Apr-16

23.56

TEST END

162

163

Testing

Cell Formulas
Range	Formula
D8	D8	=SUM($C$7:C8)-SUM($D$7:D7)
E8	E8	=+F8+G8

My Aswer Is This · Nov 16, 2022

Trebor J said:
Using my example data, above:
I started my macro, which copied in the template and requested me to enter data in cells F8 & G8.
I have done that and am in cell G8.
I now want to move down to the cell in column D that is in the row that has the date of 16/12/2015 in it.
I do not know that cells address and do not know how many rows down it is, so I am thinking that if the macro asked me to enter the date of 16/12/2015, or even just 16, it would go down to that cell in column B with that date in it, and then to column D in that row.
I would then be in the cell I want to be in, and the macro would loop back to the start.
I look forward to your scrip.
Thanks

There is a type script where if you double click on a cell something can happen
And then if you double click on another cell something else can happen. If this might work let me know with specifics

Like if I double click on A1 do this
If I double, click on B45 do this

Trebor J · Nov 16, 2022

My Aswer Is This said:
There is a type script where if you double click on a cell something can happen
And then if you double click on another cell something else can happen. If this might work let me know with specifics

Like if I double click on A1 do this
If I double, click on B45 do this

From your reply to what I have described, I am concluding that you are unable to produce scrip that will do what I want.
Is that correct or have I miss-understood you ?

My Aswer Is This · Nov 16, 2022

Trebor J said:
From your reply to what I have described, I am concluding that you are unable to produce scrip that will do what I want.
Is that correct or have I miss-understood you ?

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

Trebor J · Nov 18, 2022

Hello Readers.

After much thought and trial and error I have, myself, solved the problem I was seeking help on.

I have moved away from the concept of having the macro pause, and allow one to move the cursor to a different cell. I received a suggestion from a member of another forum, but while there where good indications that it was working, that proved not to be the case.

I then gave thought to the “Railway Line” concept and using an existing column of consecutive dates, together with entering a desired date, have managed to land in the cell I want to be in. From there the macro inserts a template which produces a Period End Total of Consumption, allows one to enter Invoiced Consumption, and shows the difference between the two. It then repeats for the next period, and gives a warning when near the end of the data.

A sample of the worksheet is below, followed by the code.

Any suggested refinements are welcomed.

Cheers, T J

Inserting a Period Total Formula and Entering Comparative Data 20221118 1936.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

2

Data for Demonstrating the Macro for Inserting a Period Total Formual and Entering Comparative Data

The OBJECTIVE of this Macro to: add up the consumption for each billing period and compare it with the consumption that has been invoiced.

3

4

DATE

Daily kW hrs

Period Total kW hrs

Invoiced kWh

5

Total

u49

u64

6

<-- Be in column D and Click on the START button to commence

7

8

16-Nov-15

28.90

560.00

530

30

Period Total Formula

9

17-Nov-15

17.70

10

18-Nov-15

22.84

START the macro from the first Cell in COLUMN D that you wish to insert a Period Total Formula

11

19-Nov-15

26.29

12

20-Nov-15

23.43

13

21-Nov-15

22.63

14

22-Nov-15

14.17

The Macro will copy in the Template from cells D8:G9, and then take you to the Input Cells. Just enter any number into each of those 2 cells, and then follow the Messages to go through the proceedure.

15

23-Nov-15

21.65

16

24-Nov-15

12.54

17

25-Nov-15

11.35

18

26-Nov-15

34.90

19

27-Nov-15

46.42

20

28-Nov-15

51.17

When you approach the bottom of the data you will be given a warning. You may not be able to see the date for the last row of the data, so you should make a note of it before commencing.

21

29-Nov-15

50.52

22

30-Nov-15

38.09

23

01-Dec-15

29.71

24

02-Dec-15

35.24

25

03-Dec-15

44.87

26

04-Dec-15

43.85

Range Names & Locations

27

05-Dec-15

40.08

28

06-Dec-15

39.68

29

07-Dec-15

48.57

FinalPeriodTotalsFormulas

=Testing!$D$8:$G$8

30

08-Dec-15

39.57

31

09-Dec-15

38.32

PeriodTotalsFormulas

=Testing!$D$8:$G$9

32

10-Dec-15

34.87

33

11-Dec-15

28.82

34

12-Dec-15

29.97

35

13-Dec-15

32.39

36

14-Dec-15

41.39

37

15-Dec-15

42.27

38

16-Dec-15

32.11

39

17-Dec-15

39.25

40

18-Dec-15

48.59

41

19-Dec-15

44.95

42

20-Dec-15

41.10

43

21-Dec-15

32.45

44

22-Dec-15

36.08

45

23-Dec-15

42.15

46

24-Dec-15

40.62

47

25-Dec-15

33.86

48

26-Dec-15

26.67

49

27-Dec-15

29.36

50

28-Dec-15

31.38

51

29-Dec-15

28.15

52

30-Dec-15

29.76

53

31-Dec-15

34.27

54

01-Jan-16

24.19

55

02-Jan-16

27.83

56

03-Jan-16

25.08

57

04-Jan-16

49.32

58

05-Jan-16

41.48

59

06-Jan-16

34.25

60

07-Jan-16

28.42

61

08-Jan-16

28.43

62

09-Jan-16

34.72

63

10-Jan-16

21.53

64

11-Jan-16

33.26

65

12-Jan-16

30.88

66

13-Jan-16

32.73

67

14-Jan-16

34.51

68

15-Jan-16

32.07

69

16-Jan-16

30.02

70

17-Jan-16

33.26

71

18-Jan-16

24.31

72

19-Jan-16

20.14

73

20-Jan-16

19.52

74

21-Jan-16

19.13

75

22-Jan-16

22.89

76

23-Jan-16

27.83

77

24-Jan-16

29.13

78

25-Jan-16

25.72

79

26-Jan-16

32.10

80

27-Jan-16

28.36

81

28-Jan-16

23.22

82

29-Jan-16

32.54

83

30-Jan-16

26.29

84

31-Jan-16

27.35

85

01-Feb-16

24.21

86

02-Feb-16

29.18

87

03-Feb-16

27.22

88

04-Feb-16

30.62

89

05-Feb-16

32.56

90

06-Feb-16

29.34

91

07-Feb-16

25.93

92

08-Feb-16

30.58

93

09-Feb-16

29.40

94

10-Feb-16

25.25

95

11-Feb-16

30.79

96

12-Feb-16

27.29

97

13-Feb-16

26.08

98

14-Feb-16

27.79

99

15-Feb-16

28.23

100

16-Feb-16

25.79

101

17-Feb-16

28.12

102

18-Feb-16

27.50

103

19-Feb-16

22.92

104

20-Feb-16

28.76

105

21-Feb-16

27.85

106

22-Feb-16

25.28

107

23-Feb-16

28.63

108

24-Feb-16

26.60

109

25-Feb-16

33.88

110

26-Feb-16

27.21

111

27-Feb-16

25.48

112

28-Feb-16

27.48

113

29-Feb-16

29.00

114

01-Mar-16

24.01

115

02-Mar-16

29.31

116

03-Mar-16

27.85

117

04-Mar-16

23.21

118

05-Mar-16

22.86

119

06-Mar-16

27.91

120

07-Mar-16

27.11

121

08-Mar-16

30.77

122

09-Mar-16

28.07

123

10-Mar-16

24.27

124

11-Mar-16

25.00

125

12-Mar-16

30.45

126

13-Mar-16

26.99

127

14-Mar-16

32.15

128

15-Mar-16

28.89

129

16-Mar-16

27.65

130

17-Mar-16

29.04

131

18-Mar-16

28.74

132

19-Mar-16

33.60

133

20-Mar-16

29.67

134

21-Mar-16

26.62

135

22-Mar-16

32.27

136

23-Mar-16

27.61

137

24-Mar-16

26.75

138

25-Mar-16

26.52

139

26-Mar-16

31.65

140

27-Mar-16

23.74

141

28-Mar-16

29.15

142

29-Mar-16

28.04

143

30-Mar-16

28.29

144

31-Mar-16

25.82

145

01-Apr-16

27.32

146

02-Apr-16

28.14

147

03-Apr-16

30.21

148

04-Apr-16

28.20

149

05-Apr-16

33.65

150

06-Apr-16

31.01

151

07-Apr-16

26.81

152

08-Apr-16

27.10

153

09-Apr-16

31.83

154

10-Apr-16

25.37

155

11-Apr-16

27.50

156

12-Apr-16

33.30

157

13-Apr-16

34.12

158

14-Apr-16

27.30

159

15-Apr-16

23.56

160

16-Apr-16

23.56

161

DATA END

162

163

164

kW hrs

165

166

Zero Proofs

4,566.52

-

167

168

169

Demo

Cell Formulas
Range	Formula
D8	D8	=SUM($C$7:C8)-SUM($D$7:D7)
E8	E8	=+F8+G8
C164:G164	C164	=SUM(C8:C163)
C166	C166	=+C164-D164
E166	E166	=+E164-F164-G164

Named Ranges
Name	Refers To	Cells
FinalPeriodTotalsFormulas	=Demo!$D$8:$G$8	D164
PeriodTotalsFormulas	=Demo!$D$8:$G$9	D164

VBA Code:

Sub CopyInPeriodTotalsFormulas()

Dim MykWh1 As Variant
Dim MykWh2 As Variant

Dim vbResult As VbMsgBoxResult

ActiveCell.Offset(0, 0).Select
Range("PeriodTotalsFormulas").Copy Destination:=ActiveCell.Offset(0, 0) ' Copy in the PeriodTotalFormula into the current cell

ActiveCell.Offset(0, 2).Select
MykWh1 = InputBox("Enter the kWh for 49")
ActiveCell.Value = MykWh1

ActiveCell.Offset(0, 1).Select
MykWh2 = InputBox("Enter the kWh for 64")
ActiveCell.Value = MykWh2

ActiveCell.Offset(1, -3).Select

Call CompareDates

End Sub

Sub CompareDates()

Dim MyDate As Date
Dim vbResult As VbMsgBoxResult
Dim Ans As Long

ActiveCell.Select
ActiveCell = MyDate
MyDate = InputBox("Enter the Date for the Start of the Next Period")
ActiveCell.Value = MyDate

If IsEmpty(ActiveCell.Offset(30, -1)) _
Then
Ans = MsgBox(" *** You are Near THE END *** " & Chr(10) & Chr(10) _
& " *** You MUST END the Macro NOW *** ", vbYesNo, "CONTINUE Y/N?")
If Ans = vbYes Then
Call CopyInFinalPeriodTotals
End If
Exit Sub

Else
Do Until ActiveCell.Offset(0, -2) = MyDate _

ActiveCell.Offset(1, 0).Select

Loop

End If

ActiveCell = ("Next") 'Labels the next cell for entry & to faciliate railwayline proceedure
Selection.End(xlUp).Select
ActiveCell.ClearContents 'Clear the contents from the MyDate cell
Selection.End(xlDown).Select

Call CopyInPeriodTotalsFormulas

End Sub

Sub CopyInFinalPeriodTotals()

ActiveCell.Offset(0, -1).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select

Range("FinalPeriodTotalsFormulas").Copy Destination:=ActiveCell.Offset(0, 0)

ActiveCell.Offset(0, 2).Select
MykWh1 = InputBox("Enter the kWh for 49")
ActiveCell.Value = MykWh1

ActiveCell.Offset(0, 1).Select
MykWh2 = InputBox("Enter the kWh for 64")
ActiveCell.Value = MykWh2

ActiveCell.Offset(0, -3).Select

Selection.End(xlUp).Select
ActiveCell.ClearContents 'Clear the contents from the MyDate cell
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select

Exit Sub

End Sub

'*************************************************************************************************************

'Sub PauseMacroToMoveCursorToAnotherCell()

'' Appears to work, but not quite.

' vbResult = MsgBox(" Move to Approx Next Period End ", vbYesNo, "CONTINUE Y/N?")
'
' If vbResult = vbYes _
' Then
' ActiveCell.Offset(29, 0).Select 'Move to next approximate cell below
'
' Dim Rng As Range
' On Error Resume Next
' Set Rng = Application.InputBox("Enter to Continue " & _
' "or Select a cell with the Mouse", _
' "Cell entry", ActiveCell.Address, , , , , 8)
' If Rng Is Nothing Then
' MsgBox "You cancelled!"
' Else
' MsgBox "Working with cell " & Rng(1).Address
'
' End If
'
' Else
' Exit Do
' End If
'End Sub

Code:

How do you pause a Macro to move the cursor from the cell it is on to another cell

Trebor J

New Member

My Aswer Is This

Well-known Member

Excel Facts

Trebor J

New Member

My Aswer Is This

Well-known Member

Trebor J

New Member

Similar threads

Forum statistics

Share this page

How do you pause a Macro to move the cursor from the cell it is on to another cell

Trebor J

New Member

My Aswer Is This

Well-known Member

Excel Facts

Trebor J

New Member

My Aswer Is This

Well-known Member

Trebor J

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock