Share Workbook - MergeCells Property Error

stucamps

Board Regular
Joined
Jul 3, 2003
Messages
114
I have a macro that errors out when I share the workbook. But when the workbook is not shared the macro runs as required.

When in Shared Workbook running the macro returns:
"Run-time error '1004': Unable to set the MergeCells property of the Range Class."

The problem is it only errors when I am sharing the workbook: therefore, I can not run the debug to examine where the error is.
When I unshare the workbook the macro works perfectly so I can not run debugging on the error.

The code basically retrieves some info that has been selected in one userform then loads and populates another userform.
Code:
Code:
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Application.ScreenUpdating = False

If ListBox1.ListIndex = -1 Then
   MsgBox ("Nothing selected in listbox. Please make a selection.")
   Exit Sub
End If

If ListBox1.Value = "" Then
   MsgBox ("Nothing selected in listbox. Please make a selection.")
   Exit Sub
End If


If ListBox1.Value = "Date" Then
    MsgBox ("You have selected the header. Please make another selection.")
    Exit Sub
End If

Dim WhichSheet As String
WhichSheet = ComboBox1.Value

Dim FindApplix As String
FindDates = Me.ListBox1.Column(0)
FindApplix = Me.ListBox1.Column(1)
FindPriority = Me.ListBox1.Column(2)
FindRequest = Me.ListBox1.Column(3)
FindDescription = Me.ListBox1.Column(4)
FindStatus = Me.ListBox1.Column(5)
FindSSC = Me.ListBox1.Column(6)
FindClient = Me.ListBox1.Column(7)
    
 Sheets(WhichSheet).Activate
 Sheets(WhichSheet).Range("B1").Select

    Sheets(WhichSheet).Range("B1", Selection.End(xlDown)) _
.Find(what:=FindApplix, After:=ActiveCell, LookIn:=xlValues, lookat:=xlPart, _
searchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False).Select

ThisWorkbook.Sheets(1).Range("A1").Value = FindDates
ThisWorkbook.Sheets(1).Range("B1").Value = FindApplix
ThisWorkbook.Sheets(1).Range("C1").Value = FindPriority
ThisWorkbook.Sheets(1).Range("D1").Value = FindRequest
ThisWorkbook.Sheets(1).Range("E1").Value = FindDescription
ThisWorkbook.Sheets(1).Range("F1").Value = FindStatus
ThisWorkbook.Sheets(1).Range("G1").Value = FindSSC
ThisWorkbook.Sheets(1).Range("H1").Value = FindClient

Set rng = Range(ThisWorkbook.Sheets(1).Range("A1"), _
ThisWorkbook.Sheets(1).Range("H1"))

With rng
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
End With

Application.ScreenUpdating = True
AntEventRet.Show


End Sub

Thanks
Stuart
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,439
Office Version
  1. 365
Platform
  1. Windows
Stuart

I am not sure but I am guessing that part of this code at least is a recorded macro? Do you actually have any merged cells that you want to unmerge? If not, try removing (or commenting out) the line
Code:
.MergeCells = False
 

Forum statistics

Threads
1,136,579
Messages
5,676,652
Members
419,638
Latest member
GlenMc52

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top