VBA: 2 Subs in Same Module, but Cannot Run Them as Macro Box is Empty

[AnyaK]

Hi,

I'd really appreciate some help.

I've written two Subs in a single module for Sheet 4. This module seems to compile fine. Both Subs are public.

When I try to run this I get a window asking for the macro name, but there are no macros shown to choose from.

I tortured myself looking online for answers and got as far as a new module with:

Sub Blah()
Call Chocolate
End Sub

But I get an "Argument not optional" error.

I don't actually know VBA, yet, so I don't know what to do to resolve this.

I just want my 2 Subs to run automatically from when the Excel .xlsm file is opened.

Please can someone advise me where I'm going wrong?

(Also, just so you know, I have learning difficulties so apologies if I'm missing the glaringly obvious.)

Thanks,

AnyaK

 

[Joe4]

If you want the Macros to be run automatically upon opening a file, you would use a Workbook_Open event procedure, which would be placed in the "ThisWorkbook" module (see: Bhttps://www.excel-easy.com/vba/events.html).

If you want modules to be seen from the Run Macro box, put them in a General Module (NOT a sheet module), and make sure that they are not marked "Private".

But I get an "Argument not optional" error.

This could imply that the macro you are trying to call is expecting a parameter to be passed that you are not providing. If you are unsure, please post all your VBA code.

 

[AnyaK]

Hi Joe4,

I'm really sorry but I don't understand at all. I have multiple learning difficulties so cannot understand things in the way that others seem to. I'm also a mid-functioning autistic.

Here is the code I want to run from when the xlsm file is opened. (I tried looking at the link you kindly posted but I didn't understand it. I am not clever.)

Sub Macro2(ByVal Target As Range)
'calculate if a cell is not blank
If Target.Column = 1 Then
If (Not (IsEmpty("B8"))) Then
ActiveCell.Formula = "=MAX(A8:A" + CStr(ActiveCell.Row - 1) + ")+1"
ActiveCell.Formula = ActiveCell.Value
'Convert formula to a value
End If
End If
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+n


End Sub


Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To allow multiple selections in a Drop Down List in Excel (without repetition)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 11 Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & vbNewLine & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.Enable
End Sub

Really sorry.

 

[Joe4]

Where did you come up with that code? It looks like you made some edits that you cannot make.

There are two basic kinds of VBA code:
- Event Procedure VBA code: This is automated VBA code that runs on some event happening (like the opening of a file, the changing of a cell value, etc). These have very rigid requirements in that that they MUST be placed in the correct module (the ThisWorkbook or a specific sheet module) and they MUST be named a certain way. These are always marked as "Private", as since they run automatically, they do not need to be seen from the Run Macros menu.
- General VBA procedure: This is one that you create, where you have the flexibility to name it as you like. These only run when they are called manually, by a button, or from another VBA procedure. You can either mark them as Private or Public. These are typically put in general VBA module (in the VB Project Explorer, right-click and select Insert -> Module).

You have one macro named "Sub Worksheet_Change(ByVal Target As Range)". This looks like it should be an event procedure, so it should be placed in the Sheet that you want it to run against, and it needs to be named like this:

Private Sub Worksheet_Change(ByVal Target As Range)

Do NOT change anything in that title! It needs to be exactly as shown, no alterations!

Your other VBA code starts:

Sub Macro2(ByVal Target As Range)

So, you have that set so a range value MUST be passed along when calling it. I don't think you intentionally meant to do that. I am guessing that you copied the header row format of an event procedure. If the code doesn't require any values to be passed along to it, remove the parameters it is requiring (the part in parentheses). So it should look like this:

Sub Macro2()

Typically, you would find this code in a General Module. But if it is being called by an Event Procedure, and that is the only thing calling it, it would not be unusual to see it in the same Sheet Module as the Event Procedure.

So, those are a few of the things you need to clean up.

To read up more on Event Procedures, see: http://www.cpearson.com/excel/Events.aspx

If you find all this is overwhelming, you may want to take a step back, and educate yourself on VBA first, so all this doesn't seem so confusing. Or maybe enlist the help of a consultant to help you get through it, if that seems like a better idea.

 

[AnyaK]

Thanks so much Joe4, I followed your instructions and it now works perfectly.

(I would like VBA training but haven't been able to find anyone to teach me despite asking locally. I'll try posting elsewhere on this forum to see if I can find a tutor.)

Thanks again for your time and clear and thorough guidance and support.

AnyaK

 

[Joe4]

Thanks so much Joe4, I followed your instructions and it now works perfectly.

You are welcome.

(I would like VBA training but haven't been able to find anyone to teach me despite asking locally. I'll try posting elsewhere on this forum to see if I can find a tutor.)

We really do not offer any tutor services, but you can find some good programs and tutorials on-line. Maybe try Googling some of those (Google "excel vba tutorial"), and see if any of those options look like they might work.