Macro doesn't work using relative cells

[mdp76]

Hi there, I am trying the macros from the beginning of the book "VBA AND MACROS". When I tried to record the 2nd (Using relative references) and the 3rd (Not using AutoSum), the macros does not work, it load the invoice file but there is no formatting, and the "Design Mode" icon gets activated.
If I run the Macro using F8 from the VB editor the program runs the way its supposed to but it doesn't run using the shortcut.

Any help is appreciated.
Thank you

(Using Excel 2007)

 

[craig.penny]

Welcome!

So you have code that sets a shortcut key for the procedure? Can you post it?

 

[mdp76]

Hi craig.penny
This is the code for the macro that uses relative ref. & no using "AutoSum"


Sub ImportInvNoAutoSum()
'
' ImportInvNoAutoSum Macro
'
' Keyboard Shortcut: Ctrl+Shift+K
'
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\QC\My Documents\PROGRAMMING\VBA+MACROS\VBA2010Files\invoice.txt" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 3), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), _
TrailingMinusNumbers:=True
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Total"
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R2C:R[-1]C)"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:C1"), Type:= _
xlFillDefault
ActiveCell.Range("A1:C1").Select
ActiveCell.Rows("1:1").EntireRow.Select
ActiveCell.Activate
Selection.Font.Bold = True
Rows("1:1").Select
Selection.Font.Bold = True
Cells.Select
Selection.Columns.AutoFit
Range("A1").Select
End Sub


Thanks, I appreciate it!

 

[craig.penny]

Try making a separate sub that sets the hotkeys. Something like

Sub setKeys()
      Application.OnKey "^+{k}", "ImportInvNoAutoSum" 'ctrl-shift-k
End Sub

then run it when you want to have that key combination run "ImportInvNoAutoSum". Before you close out of your workbook it's a good idea to reset keys back to their defaults with

Sub unSetKeys()
      Application.OnKey "^+{k}", "" 'ctrl-shift-k
End Sub

If it still doesn't work let me know.

 

[mdp76]

Hi again, I tried the code to set the hotkeys (I am new to VBA, I created a new module, copied the code and ran it) and it still doesn't work using shortcuts, if I run the code using F8 then it works. I did exactly as the book says (VBA and Macros) I was wondering if it had to do with Excel settings.


Thanks again!

 

[craig.penny]

When you try the hotkeys are you on the Excel worksheet or are you in the code module? Do you get a pop up message when you try it?

 

[mdp76]

Hi again
- I try the shortcut (Crtl+Shift+k) while I am on the Excel sheet
- No popups

 

[craig.penny]

Did you move that code into the worksheet module?

 

[mdp76]

Did you move that code into the worksheet module?

I wasn't sure how to use the code, so I tried pasting it right after the first sub, and before, It didn't work, so I created a new module and pasted it there, then run it with f5, go to my excel sheet and use the short cut (Ctrl+Shift+k)

 

[craig.penny]

And it still isn't working?

Do this for me: paste the following code into your standard module and step through the first one with F8 so you're seeing each line executed to be certain it's running. Then go to a worksheet and press Ctrl-Shift-j and see if the message box comes up.

Sub setKeys2()
  Application.OnKey "^+{j}", "TestHotKeys"
End Sub
Sub TestHotKeys()
  MsgBox "Success!"
End Sub