CODEWITHSUNDEEP
windowsmacosexcelms-officevba
Gaurav Dave
Gaurav Dave

Reputation: 7474

Macros working fine on Windows but fails on Mac

I have created a Macros, which basically get executed when any cell inside a particular range is selected. Here is my code:

Private cellSelected As String
Private cellDData As String
Private cellEData As String
Private cellFData As String
Private cellGData As String
Private cellIData As String
Private cellD As String
Private cellE As String
Private cellF As String
Private cellG As String
Private cellI As String


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

' [START] Transaction related to Location Master Form
If Not Intersect(Target, Range("B2:B300")) Is Nothing Then
    cellSelected = Target.Address(0, 0)
    Location_Master.loc_position = cellSelected
    Location_Master.Show
End If

If Not Intersect(Target, Range("C2:C300")) Is Nothing Then
    cellSelected = Target.Address(0, 0)
    Location_Master.loc_position = cellSelected
    Location_Master.Show
End If
' [END] Transaction related to Location Master Form

' [START] Transaction related to Data Master Form
If Not Intersect(Target, Range("D2:D300")) Is Nothing Then
    cellSelected = Target.Address(0, 0)
    cellE = Replace(cellSelected, "D", "E")
    cellF = Replace(cellSelected, "D", "F")
    cellG = Replace(cellSelected, "D", "G")
    cellI = Replace(cellSelected, "D", "I")

    cellDData = Range(cellSelected).Value
    cellEData = Range(cellE).Value
    cellFData = Range(cellF).Value
    cellGData = Range(cellG).Value
    cellIData = Range(cellI).Value

    Data_Master.ppr_position = cellSelected
    Data_Master.cellD_Data = cellDData
    Data_Master.cellE_Data = cellEData
    Data_Master.cellF_Data = cellFData
    Data_Master.cellG_Data = cellGData
    Data_Master.cellI_Data = cellIData
    Data_Master.Show
End If

If Not Intersect(Target, Range("E2:E300")) Is Nothing Then
    cellSelected = Target.Address(0, 0)

    cellD = Replace(cellSelected, "E", "D")
    cellF = Replace(cellSelected, "E", "F")
    cellG = Replace(cellSelected, "E", "G")
    cellI = Replace(cellSelected, "E", "I")

    cellDData = Range(cellD).Value
    cellEData = Range(cellSelected).Value
    cellFData = Range(cellF).Value
    cellGData = Range(cellG).Value
    cellIData = Range(cellI).Value

    Data_Master.ppr_position = cellSelected
    Data_Master.cellD_Data = cellDData
    Data_Master.cellE_Data = cellEData
    Data_Master.cellF_Data = cellFData
    Data_Master.cellG_Data = cellGData
    Data_Master.cellI_Data = cellIData
    Data_Master.Show
End If

If Not Intersect(Target, Range("F2:F300")) Is Nothing Then
    cellSelected = Target.Address(0, 0)

    cellD = Replace(cellSelected, "F", "D")
    cellE = Replace(cellSelected, "F", "E")
    cellG = Replace(cellSelected, "F", "G")
    cellI = Replace(cellSelected, "F", "I")

    cellDData = Range(cellD).Value
    cellEData = Range(cellE).Value
    cellFData = Range(cellSelected).Value
    cellGData = Range(cellG).Value
    cellIData = Range(cellI).Value

    Data_Master.ppr_position = cellSelected
    Data_Master.cellD_Data = cellDData
    Data_Master.cellE_Data = cellEData
    Data_Master.cellF_Data = cellFData
    Data_Master.cellG_Data = cellGData
    Data_Master.cellI_Data = cellIData
    Data_Master.Show
End If

If Not Intersect(Target, Range("G2:G300")) Is Nothing Then
    cellSelected = Target.Address(0, 0)

    cellD = Replace(cellSelected, "G", "D")
    cellE = Replace(cellSelected, "G", "E")
    cellF = Replace(cellSelected, "G", "F")
    cellI = Replace(cellSelected, "G", "I")

    cellDData = Range(cellD).Value
    cellEData = Range(cellE).Value
    cellFData = Range(cellF).Value
    cellGData = Range(cellSelected).Value
    cellIData = Range(cellI).Value

    Data_Master.ppr_position = cellSelected
    Data_Master.cellD_Data = cellDData
    Data_Master.cellE_Data = cellEData
    Data_Master.cellF_Data = cellFData
    Data_Master.cellG_Data = cellGData
    Data_Master.cellI_Data = cellIData
    Data_Master.Show
End If

If Not Intersect(Target, Range("I2:I300")) Is Nothing Then
    cellSelected = Target.Address(0, 0)

    cellD = Replace(cellSelected, "I", "D")
    cellE = Replace(cellSelected, "I", "E")
    cellF = Replace(cellSelected, "I", "F")
    cellG = Replace(cellSelected, "I", "G")

    cellDData = Range(cellD).Value
    cellEData = Range(cellE).Value
    cellFData = Range(cellF).Value
    cellGData = Range(cellG).Value
    cellIData = Range(cellSelected).Value

    Data_Master.ppr_position = cellSelected
    Data_Master.cellD_Data = cellDData
    Data_Master.cellE_Data = cellEData
    Data_Master.cellF_Data = cellFData
    Data_Master.cellG_Data = cellGData
    Data_Master.cellI_Data = cellIData
    Data_Master.Show
End If
' [END] Transaction related to Data Master Form

End Sub

Here, I'm passing the selected cell value. i.e. C2, D5 etc and with that cell data if it has any. Now, this piece of code is working fine in MS Office 2010, 

Public ppr_position As String
Public cellD_Data As String
Public cellE_Data As String
Public cellF_Data As String
Public cellG_Data As String
Public cellI_Data As String
Private cellLetter As String
Private cellD As String
Private cellE As String
Private cellF As String
Private cellG As String
Private cellI As String

Private Sub btnCancel_Click()
    Unload Me ' Unloading the Data Master Form
End Sub

Private Sub btnSubmit_Click()
    cellLetter = Left(ppr_position, 1) 'Getting Cell Letter where user just clicked

    If (StrComp(cellLetter, "D") = 0) Then ' Paper Type was selected
        cellE = Replace(ppr_position, "D", "E")
        cellF = Replace(ppr_position, "D", "F")
        cellG = Replace(ppr_position, "D", "G")
        cellI = Replace(ppr_position, "D", "I")

            ' Applying Validation on data entered
            If (Me.paper_type_list.ListIndex = -1) Then
                MsgBox "Kindly Select valid Paper Type", vbCritical
                Exit Sub
            ElseIf (Me.paper_source_list.ListIndex = -1) Then
                MsgBox "Kindly Select valid Paper Source", vbCritical
                Exit Sub
            ElseIf (Me.paper_quality_list.ListIndex = -1) Then
                MsgBox "Kindly Select valid Paper Quality", vbCritical
                Exit Sub
            ElseIf (Me.stock_status_list.ListIndex = -1) Then
                MsgBox "Kindly Select valid Stock Status", vbCritical
                Exit Sub
            ElseIf (Me.currency_list.ListIndex = -1) Then
                MsgBox "Kindly Select valid Currency", vbCritical
                Exit Sub
            End If

        ' Data gets entered to corresponding cell(s) on Validation Success
        Range(ppr_position).Value = Me.paper_type_list
        Range(cellE).Value = Me.paper_source_list
        Range(cellF).Value = Me.paper_quality_list
        Range(cellG).Value = Me.stock_status_list
        Range(cellI).Value = Me.currency_list

    ElseIf (StrComp(cellLetter, "E") = 0) Then ' Paper Source was selected
        cellD = Replace(ppr_position, "E", "D")
        cellF = Replace(ppr_position, "E", "F")
        cellG = Replace(ppr_position, "E", "G")
        cellI = Replace(ppr_position, "E", "I")

            ' Applying Validation on data entered
            If (Me.paper_type_list.ListIndex = -1) Then
                MsgBox "Kindly Select valid Paper Type", vbCritical
                Exit Sub
            ElseIf (Me.paper_source_list.ListIndex = -1) Then
                MsgBox "Kindly Select valid Paper Source", vbCritical
                Exit Sub
            ElseIf (Me.paper_quality_list.ListIndex = -1) Then
                MsgBox "Kindly Select valid Paper Quality", vbCritical
                Exit Sub
            ElseIf (Me.stock_status_list.ListIndex = -1) Then
                MsgBox "Kindly Select valid Stock Status", vbCritical
                Exit Sub
            ElseIf (Me.currency_list.ListIndex = -1) Then
                MsgBox "Kindly Select valid Currency", vbCritical
                Exit Sub
            End If

        ' Data gets entered to corresponding cell(s) on Validation Success
        Range(ppr_position).Value = Me.paper_source_list
        Range(cellD).Value = Me.paper_type_list
        Range(cellF).Value = Me.paper_quality_list
        Range(cellG).Value = Me.stock_status_list
        Range(cellI).Value = Me.currency_list

    ElseIf (StrComp(cellLetter, "F") = 0) Then ' Paper Quality was selected
        cellD = Replace(ppr_position, "F", "D")
        cellE = Replace(ppr_position, "F", "E")
        cellG = Replace(ppr_position, "F", "G")
        cellI = Replace(ppr_position, "F", "I")

            ' Applying Validation on data entered
            If (Me.paper_type_list.ListIndex = -1) Then
                MsgBox "Kindly Select valid Paper Type", vbCritical
                Exit Sub
            ElseIf (Me.paper_source_list.ListIndex = -1) Then
                MsgBox "Kindly Select valid Paper Source", vbCritical
                Exit Sub
            ElseIf (Me.paper_quality_list.ListIndex = -1) Then
                MsgBox "Kindly Select valid Paper Quality", vbCritical
                Exit Sub
            ElseIf (Me.stock_status_list.ListIndex = -1) Then
                MsgBox "Kindly Select valid Stock Status", vbCritical
                Exit Sub
            ElseIf (Me.currency_list.ListIndex = -1) Then
                MsgBox "Kindly Select valid Currency", vbCritical
                Exit Sub
            End If

        ' Data gets entered to corresponding cell(s) on Validation Success
        Range(ppr_position).Value = Me.paper_quality_list
        Range(cellD).Value = Me.paper_type_list
        Range(cellE).Value = Me.paper_source_list
        Range(cellG).Value = Me.stock_status_list
        Range(cellI).Value = Me.currency_list

    ElseIf (StrComp(cellLetter, "G") = 0) Then ' Stock Status was selected
        cellD = Replace(ppr_position, "G", "D")
        cellE = Replace(ppr_position, "G", "E")
        cellF = Replace(ppr_position, "G", "F")
        cellI = Replace(ppr_position, "G", "I")

            ' Applying Validation on data entered
            If (Me.paper_type_list.ListIndex = -1) Then
                MsgBox "Kindly Select valid Paper Type", vbCritical
                Exit Sub
            ElseIf (Me.paper_source_list.ListIndex = -1) Then
                MsgBox "Kindly Select valid Paper Source", vbCritical
                Exit Sub
            ElseIf (Me.paper_quality_list.ListIndex = -1) Then
                MsgBox "Kindly Select valid Paper Quality", vbCritical
                Exit Sub
            ElseIf (Me.stock_status_list.ListIndex = -1) Then
                MsgBox "Kindly Select valid Stock Status", vbCritical
                Exit Sub
            ElseIf (Me.currency_list.ListIndex = -1) Then
                MsgBox "Kindly Select valid Currency", vbCritical
                Exit Sub
            End If

        ' Data gets entered to corresponding cell(s) on Validation Success
        Range(ppr_position).Value = Me.stock_status_list
        Range(cellD).Value = Me.paper_type_list
        Range(cellE).Value = Me.paper_source_list
        Range(cellF).Value = Me.paper_quality_list
        Range(cellI).Value = Me.currency_list

    ElseIf (StrComp(cellLetter, "I") = 0) Then ' Currency was selected
        cellD = Replace(ppr_position, "I", "D")
        cellE = Replace(ppr_position, "I", "E")
        cellF = Replace(ppr_position, "I", "F")
        cellG = Replace(ppr_position, "I", "G")

            ' Applying Validation on data entered
            If (Me.paper_type_list.ListIndex = -1) Then
                MsgBox "Kindly Select valid Paper Type", vbCritical
                Exit Sub
            ElseIf (Me.paper_source_list.ListIndex = -1) Then
                MsgBox "Kindly Select valid Paper Source", vbCritical
                Exit Sub
            ElseIf (Me.paper_quality_list.ListIndex = -1) Then
                MsgBox "Kindly Select valid Paper Quality", vbCritical
                Exit Sub
            ElseIf (Me.stock_status_list.ListIndex = -1) Then
                MsgBox "Kindly Select valid Stock Status", vbCritical
                Exit Sub
            ElseIf (Me.currency_list.ListIndex = -1) Then
                MsgBox "Kindly Select valid Currency", vbCritical
                Exit Sub
            End If

        ' Data gets entered to corresponding cell(s) on Validation Success
        Range(ppr_position).Value = Me.currency_list
        Range(cellD).Value = Me.paper_type_list
        Range(cellE).Value = Me.paper_source_list
        Range(cellF).Value = Me.paper_quality_list
        Range(cellG).Value = Me.stock_status_list

    End If

    Unload Me ' Unloading the Data Master Form
End Sub

Private Sub paper_quality_list_Change()

End Sub

Private Sub paper_type_list_Change()

End Sub

Private Sub UserForm_Activate()
    Me.paper_type_list.Value = cellD_Data
    Me.paper_source_list.Value = cellE_Data
    Me.paper_quality_list.Value = cellF_Data
    Me.stock_status_list.Value = cellG_Data
    Me.currency_list.Value = cellI_Data
End Sub

Private Sub UserForm_Initialize()

    ' Setting Paper Type Name Range to combo-box
    For Each paperType In [Paper_Type]
        Me.paper_type_list.AddItem paperType
    Next paperType

    ' Setting Paper Source Name Range to combo-box
    For Each paperSource In [Paper_Source]
        Me.paper_source_list.AddItem paperSource
    Next paperSource

    ' Setting Paper Quality Name Range to combo-box
    For Each paperQuality In [Paper_Quality]
        Me.paper_quality_list.AddItem paperQuality
    Next paperQuality

    ' Setting Stock Status Name Range to combo-box
    For Each stockStatus In [Stock_Status]
        Me.stock_status_list.AddItem stockStatus
    Next stockStatus

    ' Setting Currency Name Range to combo-box
    For Each currencyName In [Currency]
        Me.currency_list.AddItem currencyName
    Next currencyName

End Sub

but fails at:

For Each currencyName In [Currency]

Where, [Currency] is my Dynamic Name Range. (I am using Mac Office 2011). Please suggest any solution.

Upvotes: 1

Views: 205

Answers (1)

Gaurav Dave
Gaurav Dave

Reputation: 7474

Used:

Worksheets("Paper Master").Range("Paper_Quality") instead of [Paper_Quality]

and is working fine on Mac.

Upvotes: 1

Related Questions