Run Time Error 6028: Help?

[Dan the Man]

Don't know if anyone can help me with this or not.

I have a Userform on my spreadsheet for inputting "comments" into. This form
also has a variety of function buttons on it which work just fine, with the
exception of 2 of those buttons (Post Comments-Button 3, and Update
Comments-Button 4).

Whenever I attempt to "click" on either Post or Update Comments, I receive
the following popup: "Run Time Error 6028: The Range Cannot Be Deleted". I'm
not exactly sure what this message means, or how to rectify the problem.
This code exists in my "Word" VB editor. The line of code (for each command
button) that highlights in "yellow" (where I suspect is the problem) is:

mytable.Cell(nrows + 1, 1) = TextBox1.Text

If someone could give me an idea of what I need to look for in order to
attack this problem I would appreciate it. I'm a "noobie" to all of this so
I'm sure that doesn't help. Below is the full code information. Thanks in
advance!

Dan

--------------------------------------------------------------------------------------------------------
Public nrows As Integer
Public nr As Integer
Public incr As Integer
Public mytable As Object

Private Sub cmbNext_Click()
nr = nr + 1
cmbNext.Visible = True
cmbPrevious.Visible = True
If nr >= nrows Then
cmbNext.Visible = False
End If
With mytable
TextBox2.Text = Left(.Cell(nr + 1, 2), Len(.Cell(nr + 1, 2)) - 2)
TextBox1.Text = Left(.Cell(nr + 1, 1), Len(.Cell(nr + 1, 1)) - 2)
End With
End Sub
Private Sub cmbPrevious_Click()
nr = nr - 1
cmbNext.Visible = True
cmbPrevious.Visible = True
If nr = 1 Then
cmbPrevious.Visible = False
End If
With mytable
TextBox2.Text = Left(.Cell(nr + 1, 2), Len(.Cell(nr + 1, 2)) - 2)
TextBox1.Text = Left(.Cell(nr + 1, 1), Len(.Cell(nr + 1, 1)) - 2)
End With
End Sub

Private Sub cmbPrintComment_Click()

Documents.Add DocumentType:=wdNewBlankDocument

Selection.TypeText Text:="Client Name: " & TextBox3.Text
Selection.TypeParagraph
Selection.TypeParagraph
Selection.TypeText Text:="Date of Progress Note: " & TextBox2.Text
Selection.TypeParagraph
Selection.TypeParagraph
Selection.TypeText Text:=TextBox1.Text


Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="",
PageType:=wdPrintAllPages, _
ManualDuplexPrint:=False, Collate:=True, Background:=True,
PrintToFile:= _
False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
PrintZoomPaperHeight:=0


End Sub

Private Sub cmbPrintAll_Click()
Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="",
PageType:=wdPrintAllPages, _
ManualDuplexPrint:=False, Collate:=True, Background:=True,
PrintToFile:= _
False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
PrintZoomPaperHeight:=0
End Sub


Private Sub CommandButton11_Click()
TextBox1.Text = ""
End Sub

Private Sub UserForm_Initialize()
Set mytable = ActiveDocument.Tables(1)
With mytable
TextBox3.Text = Left(.Cell(1, 1), Len(.Cell(1, 1)) - 2)
TextBox2.Text = Left(.Cell(2, 2), Len(.Cell(2, 2)) - 2)
TextBox1.Text = Left(.Cell(2, 1), Len(.Cell(2, 1)) - 2)
nr = 1
nrows = 0
incr = 1
For n = 2 To .Rows.Count
If Len(.Cell(n, 1)) <> 2 Then
nrows = nrows + 1
End If
Next n
cmbPrevious.Visible = False

End With
End Sub

Private Sub CommandButton3_Click()
nrows = nrows + 1
mytable.Cell(nrows + 1, 1) = TextBox1.Text
mytable.Cell(nrows + 1, 2) = Now()
incr = 1
nr = nrows
End Sub
Private Sub CommandButton4_Click()
nrows = nr
mytable.Cell(nrows + 1, 1) = TextBox1.Text
mytable.Cell(nrows + 1, 2) = Now()
End Sub
Private Sub CommandButton5_Click()
mytable.Cell(nr + 1, 1).Row.Delete
TextBox1.Text = ""
TextBox2.Text = ""
nrows = nrows - 1
End Sub
Private Sub CommandButton8_Click()
UserForm1.hide
End Sub

 

[Joel]

I don't know why 5 isn't also failing. I think you just forgot to set
mytable or assumed because you had the Public declaration that it would
over-ride the private statements in each of the sub statements.

Set mytable = ActiveDocument.Tables(1)


Each subrouting in your code is private. They will not share variables even
though you have the Public statements. You may just want to remove private
from all the sub statements.

 

[Dan the Man]

Hi Joel!

I'm a REAL NOOBIE when it comes to programming. Just to clarify your
suggestion:

Your idea is for me to remove the word "Private" from all code, and this
should resolve my Runtime Error problem?

Instead of: Private Sub CommandButton3_Click()
Use: Sub CommandButton3_Click()

Thanks for the clarification!

Dan

 

[Tom Ogilvy]

do you have an embedded spreadsheet in your word document or are you not
working with excel at all? If not working with Excel at all (which it sounds
like), then perhaps you should ask this in a word group.

 

[Joel]

Yes. if you have private in the sub then they can't share public variables.

 

[Dan the Man]

Actually I am working with Excel and Word. I have an Excel Spreadsheet, and a
Macro button which brings up a dropdown list of names. When a name is chosen,
Excel calls Word, and brings up my Userform. The Userform works off of Word
(versus Excel).

The odd thing about this is that a previous version of this (spreadsheet and
Word based Userform) is that everything worked PERFECTLY. Some of the code
was adjusted in order to automatically create the Word User documents, when a
name was selected from the Excel spreadsheet. It was this change which seems
to have triggered the runtime error. Previous to this, all function buttons
worked just fine. The other ironic thing is this: "the code for the
Userform-where the function buttons work off of) has NOT changed.

Code alterations were made to Excel:

Userform 3-which references the dropdown list of names on my Excel
Spreadsheet, allowing me to chose a name from a dropdown list and open the
Word based Userform.

Module 2-the code which creates the individual Word Documents for inputing
comments (using the Userform).

Code alterations were made to Word:

Module 1 "Normal"-which interacts with Excel, allowing the Userform to be
called up (again I'm a noobie so I'm not 100% as to how this code interacts
however I believe it is designed to check Word to see if a Word document has
previously been created for a particular person, or needs to be created).

The code which impacts the actual Userform interface (Userform 1) for
inputting comments has NOT changed, and thus I am perplexed as to why I am
getting the Runtime 6028 error here (the debugger only highlights the code
associated with this Userform that I previously mentioned and posted a copy
of).

Dan

 

[Jim Cone]

Add Option Explicit as the first line in all modules.
You may be surprised at what gets flagged.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Dan the Man"
wrote in message
Don't know if anyone can help me with this or not.
I have a Userform on my spreadsheet for inputting "comments" into. This form
also has a variety of function buttons on it which work just fine, with the
exception of 2 of those buttons (Post Comments-Button 3, and Update
Comments-Button 4).
Whenever I attempt to "click" on either Post or Update Comments, I receive
the following popup: "Run Time Error 6028: The Range Cannot Be Deleted". I'm
not exactly sure what this message means, or how to rectify the problem.
This code exists in my "Word" VB editor. The line of code (for each command
button) that highlights in "yellow" (where I suspect is the problem) is:

mytable.Cell(nrows + 1, 1) = TextBox1.Text
-snip-

 

[Dan the Man]

Hi Jim!

Being a "noobie" at this could you be a little more specific and include an
example. I wasn't sure what you meant by "add option explicit at the first
line in all modules". Thanks much.

Dan

 

[Jim Cone]

It is difficult to be more explicit, but...
Type "Option Explicit" without the quote marks.
Add it above everything else in each module.
Then compile the module... (Debug | Compile)
It will identify all undeclared variables.
Take a look in help for more info.

It may not be flagged, but every instance of "Cell" should be "Cells"
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Dan the Man"
wrote in message
Hi Jim!

Being a "noobie" at this could you be a little more specific and include an
example. I wasn't sure what you meant by "add option explicit at the first
line in all modules". Thanks much.
Dan

 

[Dan the Man]

So to be sure I have this correct (with respect to Option Explicit and
changing "cell" to "cells") is below the accurate adjustment to make with
respect to the 2 fucntion buttons on my Userform that are triggering the
Runtime 6028 Error? I will be very happy if this solves the problem and
allows the code to work!
__________________________________________________
Private Sub CommandButton3_Click()
Option Explicit
nrows = nrows + 1
mytable.Cells(nrows + 1, 1).Range.Text = TextBox1.Text
mytable.Cells(nrows + 1, 2) = Now()
incr = 1
nr = nrows
End Sub
__________________________________________________
Private Sub CommandButton4_Click()
Option Explicit
nrows = nr
mytable.Cells(nrows + 1, 1).Range.Text = TextBox1.Text
mytable.Cells(nrows + 1, 2) = Now()
End Sub
_______________________________________________________________________

 

[Jim Cone]

Option Explicit goes above ALL code, not at the top of each sub.
It is only entered once.
Jim Cone




"Dan the Man"
wrote in message
So to be sure I have this correct (with respect to Option Explicit and
changing "cell" to "cells") is below the accurate adjustment to make with
respect to the 2 fucntion buttons on my Userform that are triggering the
Runtime 6028 Error? I will be very happy if this solves the problem and
allows the code to work!
__________________________________________________
Private Sub CommandButton3_Click()
Option Explicit
nrows = nrows + 1
mytable.Cells(nrows + 1, 1).Range.Text = TextBox1.Text
mytable.Cells(nrows + 1, 2) = Now()
incr = 1
nr = nrows
End Sub
__________________________________________________
Private Sub CommandButton4_Click()
Option Explicit
nrows = nr
mytable.Cells(nrows + 1, 1).Range.Text = TextBox1.Text
mytable.Cells(nrows + 1, 2) = Now()
End Sub
_______________________________________________________________________