TRYING TO UNDERSTAND HOW EXCEL VBA MACROS AND USER FORMS ARE LINKED TO EXCEL FILES

[JohnM73]

I have several similar text data-bases each held as a separate Excel 2007 xlsx file. Each uses a macro to generate a user-form to display and edit row-contents from the data-base, but I am not clear whether one copy of the macro is shared by several data-base files, or whether each has its own copy.

Recently I tidied up my files, at which point Excel, on being asked to enable macros, reported that it could no longer find a link. I put the files back to their original positions, and all worked again. Clearly I had broken and then restored a file path.

However the file path quoted by the Excel error message is not mentioned in the contents of the data-base, or the VBA text of my macro. So where is it? And when I find it, how can I edit it so that the data-base becomes portable and no longer depends on where it is placed in my system?

Any help gratefully received!

JohnM

[SamT]

*.xlsx files can't have "Macros" in them.
I have several similar text data-bases each held as a separate Excel 2007 xlsx file. Each uses a macro Are the "Data Bases" text based (*.csv files) or Excel WorkBooks, (*.xlsx, *.xlsm, or *.xlsb files)?


Also:
However the file path quoted by the Excel error message is not mentioned in the contents of the data-base, or the VBA text of my macro. So where is it?

Because of the language irregularities in your description, I can't tell for sure, but it sounds like you have Formulas in the Worksheets that reference other Workbooks.

On the other hand, if Excel is asking to enable Macros, then the Workbook you are opening is an xlsm type Workbook that does contain Code. FYI, Macros are recorded transformations of actions you take on a Worksheet and Procedures are hand written and do not contain all the superfluous Methods, Functions, and Properties that Macros do.

:dunno

Please describe the problem with more specific detail.

[JohnM73]

*.xlsx files can't have "Macros" in them. Are the "Data Bases" text based (*.csv files) or Excel WorkBooks, (*.xlsx, *.xlsm, or *.xlsb files)?


Also:

Because of the language irregularities in your description, I can't tell for sure, but it sounds like you have Formulas in the Worksheets that reference other Workbooks.

On the other hand, if Excel is asking to enable Macros, then the Workbook you are opening is an xlsm type Workbook that does contain Code. FYI, Macros are recorded transformations of actions you take on a Worksheet and Procedures are hand written and do not contain all the superfluous Methods, Functions, and Properties that Macros do.

:dunno

Please describe the problem with more specific detail.



Many thanks. I'll try to be clearer!

Following your clarification above, this is a procedure - i.e. written code in VBA - not a recorded macro, and the files are .xlsm

I've made a little further progress on my own, and the position seems to be that I have two Excel text data-bases (.xlsm files) of similar structure and both have a user-form interface driven by the same VBA procedure.

It seems that for the data-base A .xlsm file, the VBA procedure code controlling the user-form is firmly attached to it - i.e. I can move the .xlsm file around in my system, and the user-form continues to work. If I click on the Data command, the Edit Links command is greyed out.

However the data-base B .xlsm file seems to be using the VBA procedure code attached to the data-base A file. When data-base B is open and I click on Data | Edit Links it displays a link to data-base A, and if I use 'Break Link' my control button to the procedure becomes disabled. And of course the link also breaks if I move the data-base A .xlsm file (which was my original symptom).

So how do I arrange for the Data-base B .xlsm file to have its own copy of the VBA procedure currently linked to the Data-base A .xlsm file?

Advice much appreciated!

[SamT]

You will have to edit the Procedure itself. We need to see the code to know how to edit it. Code is very strict about grammar, syntax, and spelling.

Show both codes here. The easiest way is to Copy the code in the VBA editor, then, in our editor click the # icon, followed by pressing Ctrl+V.

Be sure and label the Code Blocks, (before the [ CODE ] Tag,) with Book A and Book B, and, indicate if they are in a Module or in the ThisWorkbook Code page.

[JohnM73]

You will have to edit the Procedure itself. We need to see the code to know how to edit it. Code is very strict about grammar, syntax, and spelling.

Show both codes here. The easiest way is to Copy the code in the VBA editor, then, in our editor click the # icon, followed by pressing Ctrl+V.

Be sure and label the Code Blocks, (before the [ CODE ] Tag,) with Book A and Book B, and, indicate if they are in a Module or in the ThisWorkbook Code page.


Thanks. I tried sending both code and a screen-dump of key info, but got the message:


Post denied. New posts are limited by number of URLs it may contain and checked if it doesn't contain forbidden words.


For some reason it doesn't like the code, but here at least is the screen dump.

"##Plays1940-1958.xlsm" is 'Data-base A'. "1965-1970-UsingProforma3.xlsx" is 'Data-base B'.

NB that 'data-base B' is actually an .xlsx file, not an .xlsm one, so it can't contain any VBA - sorry - my mistake in earlier messages. When I realized that I tried simply creating a .xlsm version of Data-base B, and then opening Project Explorer in Data-base A to 'Export' a copy of the code ("PlayListForm"), and then opening Project Explorer in Data-base B to 'Import' PlayListForm.frm into Data-base B. But the link to Data-base A is still there.

Can you do anything with this without the code?

Many thanks

[JohnM73]

Here is an attempt to send the code as a Word document to see if it will accept that.

[SamT]

From Word Doc
'PLAYS
'=====
'=====


'Global declarations

Public ReadVsEdit As Integer
Public imgSize As Integer



' USER FORM AND COMMAND BUTTONS
' =============================

Private Sub UserForm_Initialize()
'User Form initialization sub

ComboBox1.AddItem "READ ONLY"
ComboBox1.AddItem "EDITABLE"
ComboBox1.BoundColumn = 0 'Additems are a list starting from 0
ComboBox1.ListIndex = 0 'Display "Read Only" initially - Item 0
ReadVsEdit = 1 'Default is Read-only
imgSize = 1 'Start with small images

FirstCmdBut_Click 'Form opens showing first row

Image1.Enabled = True 'Responds to clicks
Image1.Visible = True
Image1.AutoSize = False
Image1.PictureSizeMode = fmPictureSizeModeZoom
Image1.Height = 150
Image1.Width = 210
Image1.Left = 354
Image1.Top = 288

End Sub

Private Sub ComboBox1_Change()
'Control for Edit ComboBox

If ComboBox1.Text = "READ ONLY" Then
ReadVsEdit = 1
AddCmdBut.Enabled = False
End If

[SamT]

Nope. The problem is not in that piece of code. As you can see there are no references to anything outside the UserForm.

[JohnM73]

QUOTE=SamT;337733]From Word Doc
'PLAYS
'=====
'=====


'Global declarations

Public ReadVsEdit As Integer
Public imgSize As Integer



' USER FORM AND COMMAND BUTTONS
' =============================

Private Sub UserForm_Initialize()
'User Form initialization sub

ComboBox1.AddItem "READ ONLY"
ComboBox1.AddItem "EDITABLE"
ComboBox1.BoundColumn = 0 'Additems are a list starting from 0
ComboBox1.ListIndex = 0 'Display "Read Only" initially - Item 0
ReadVsEdit = 1 'Default is Read-only
imgSize = 1 'Start with small images

FirstCmdBut_Click 'Form opens showing first row

Image1.Enabled = True 'Responds to clicks
Image1.Visible = True
Image1.AutoSize = False
Image1.PictureSizeMode = fmPictureSizeModeZoom
Image1.Height = 150
Image1.Width = 210
Image1.Left = 354
Image1.Top = 288

End Sub

Private Sub ComboBox1_Change()
'Control for Edit ComboBox

If ComboBox1.Text = "READ ONLY" Then
ReadVsEdit = 1
AddCmdBut.Enabled = False
End If
[/QUOTE]



That is only page 1 of 15 pages of code - it looks as if the forum cuts it off at the page break. But I get the gist of what you want to do, and I've been through the code and have found a couple of references to incorrect external folders, so I'll explore them a bit further.

Many thanks for your help so far - we may not have cracked it 100% yet, but you are pushing me in useful directions!

[SamT]

5 pages! Oh boy, did I mess up.
'PLAYS
'=====
'=====



'Global declarations

Public ReadVsEdit As Integer
Public imgSize As Integer



' USER FORM AND COMMAND BUTTONS
' =============================


Private Sub UserForm_Initialize()
'User Form initialization sub

ComboBox1.AddItem "READ ONLY"
ComboBox1.AddItem "EDITABLE"
ComboBox1.BoundColumn = 0 'Additems are a list starting from 0
ComboBox1.ListIndex = 0 'Display "Read Only" initially - Item 0
ReadVsEdit = 1 'Default is Read-only
imgSize = 1 'Start with small images

FirstCmdBut_Click 'Form opens showing first row

Image1.Enabled = True 'Responds to clicks
Image1.Visible = True
Image1.AutoSize = False
Image1.PictureSizeMode = fmPictureSizeModeZoom
Image1.Height = 150
Image1.Width = 210
Image1.Left = 354
Image1.Top = 288

End Sub


Private Sub ComboBox1_Change()
'Control for Edit ComboBox

If ComboBox1.Text = "READ ONLY" Then
ReadVsEdit = 1
AddCmdBut.Enabled = False
End If

If ComboBox1.Text = "EDITABLE" Then
ReadVsEdit = 2
AddCmdBut.Enabled = True
MsgBox "Warning: Enabling editing could lead to data-base corruption"
End If

End Sub


Private Sub ScrollBar2_Change()
'Scrollbar control sub

Dim LastRow As Long

ScrollBar2.Min = 1 'Left end of scrollbar = 1

LastRow = FindLastRow - 1
ScrollBar2.Max = LastRow 'Right end = LastRow

RowTxt.Value = ScrollBar2.Value 'Set current value into row number control, which
'will trigger GetData

End Sub


Private Sub FirstCmdBut_Click()
'Control to go to first entry in catalogue (Row 2)

RowTxt.Text = "2"

End Sub


Private Sub PrevCmdBut_Click()
'Control to go to previous row (exit silently if none)

Dim R As Long
Dim LastRow As Long

LastRow = FindLastRow

'Check that the text in the row mnumber box is a legal number.
'If so, 'r' becomes the row number. Otherwise exit.
If IsNumeric(RowTxt.Text) Then
R = CLng(RowTxt.Text)
R = R - 1
If R > 1 And R <= LastRow Then
RowTxt = FormatNumber(R, 0) 'The change in RowTxt value will trigger GetData
End If
End If

End Sub


Private Sub RowTxt_Change()
'Control to go to line shown in RowTxt box

Dim R As Long
Dim LastRow As Long

LastRow = FindLastRow

If IsNumeric(RowTxt.Text) = False Then Exit Sub 'Ignore if not a number
R = CLng(RowTxt.Text) 'Convert text to number
If R < 1 Then RowTxt.Value = 1 'If Row number out of range, force it into range
If R > LastRow Then RowTxt.Value = LastRow - 1

ScrollBar2.Value = RowTxt.Value 'Scrollbar slider set to current row
GetData

End Sub


Private Sub NextCmdBut_Click()
'Control to go to next row, if legal (exit silently if none)

Dim R As Long
Dim LastRow As Long

LastRow = FindLastRow

'Check that the text in the row mnumber box is a legal number.
'If so, 'r' becomes the row number. Otherwise exit.
If IsNumeric(RowTxt.Text) Then
R = CLng(RowTxt.Text)
R = R + 1
If R > 1 And R <= (LastRow - 1) Then
RowTxt = FormatNumber(R, 0) 'The change in RowTxt value will trigger GetData
End If
End If

End Sub


Private Sub LastCmdBut_Click()
'Control for 'Last' command

LastRow = FindLastRow - 1
RowTxt.Text = FormatNumber(LastRow, 0) 'Set RowTxt to last row number

End Sub


Private Sub CancelCmdBut_Click()
'Control for Cancel command

GetData 'Existing data is copied back into panel windows

End Sub


Private Sub SaveCmdBut_Click()
'Control for Save command.

PutData 'User will already have entered new data, which is now copied to database

End Sub


Private Sub AddCmdBut_Click()
'Control for Add command

LastRow = FindLastRow 'NB that FindLastRow finds value of first blank row
RowTxt.Text = FormatNumber(LastRow, 0) 'In effect, this displays the first blank row

End Sub

Private Sub FindButton_Click()
'Control for Find - also uses textbox 'Hittext'

Dim R As Long
Dim Target As String

N = CLng(RowTxt.Text) 'Current row number as shown in the row box
Target = HitText.Text 'Holds target string
R = Cells.Find(Target, Cells(N + 1, 1)).Row 'R = row number of the first hit after Row N+1
RowTxt.Text = FormatNumber(R, 0) 'Set RowTxt to row number of hit
End Sub



Private Sub Image1_Click()
'Toggles between large and small image formats

If imgSize = 2 Then
imgSize = 1
GetData
Image1.Height = 150
Image1.Width = 210
Image1.Left = 354
Image1.Top = 288
Exit Sub
ElseIf imgSize = 1 Then
imgSize = 2
GetData
Image1.Height = 660
Image1.Width = 940
Image1.Left = 5
Image1.Top = 5
Exit Sub
Else
MsgBox "Aaarghhh!"
End If

End Sub




'VARIOUS PROCESSES
'=================



Private Sub GetData()
'Subroutine to copy text entries from the catalogue entry pointed to
'in the RowTxt box, to the display panel

Dim R As Long
Dim LastRow As Long

LastRow = FindLastRow

'Check that the text in the row number box is a legal number.
'If so, 'r' becomes the row number. Otherwise exit.
If IsNumeric(RowTxt.Text) Then
R = CLng(RowTxt.Text)
Else
ClearData
MsgBox "Illegal row number"
Exit Sub
End If

If R < 1 Or R > LastRow Then
MsgBox "Row number out of range so reset to 2"
R = 2 'Force R back to legal value
End If


'Ensure that the Thumbnail box label is NOT set to quasi-hyperlink
With Label12 'Make label look normal (hyperlink would be blue and underlined)
.Font.Underline = False
.ForeColor = RGB(0, 0, 0)
End With


'R is a legal row number, so copy contents of row R into the various text windows, etc.
'NB that doing this will also trigger related Change event for that text window


PlayName.Text = Cells(R, 1) 'NB that each row will also trigger related Change event
Author.Text = Cells(R, 2)
StartDate.Text = Cells(R, 3)
NoOfPerfs.Text = Cells(R, 4)
CircaDate.Text = Cells(R, 5)
StartTime.Text = Cells(R, 6)
TicketPrices.Text = Cells(R, 7)
Venue.Text = Cells(R, 8)
TheatreSocy.Text = Cells(R, 9)
PartOf.Text = Cells(R, 10)
Description.Text = Cells(R, 11)
CastCrew.Text = Cells(R, 12)
Notes.Text = Cells(R, 13)
References.Text = Cells(R, 14)
Files.Text = Cells(R, 15)
ThumbnailFile.Text = Cells(R, 16)


'Inserting image from column 16 ("P")
DisableSave 'Since this sub is read-only, Save/Cancel greyed out

Image1.Visible = True

'b) address of cell containing image address in "A1" format. Col Letter is "P" for this catalogue, but
'has other letters for the other two lists.

imgpath = "P" + RowTxt.Text
imgpath = Range(imgpath).Text
If imgpath = "" Then
GoTo image 'Empty image address, so Display nul image
End If

If Right(imgpath, 4) <> ".jpg" Then
imgpath = "##MultiPage.jpg" 'Not a 'jpg file - read via hyperlink
With Label12 'Make label look like a hyperlink
.Font.Underline = True
.ForeColor = RGB(0, 128, 255)
End With
End If

'c) create full image path
imgpath = ActiveWorkbook.Path + "/Filestore1840-1900/" + imgpath

'd) Inserts Picture
'On Error GoTo ImageError 'E.g. case where file doesn't exist
Image1.Visible = True
image: Image1.Picture = LoadPicture(imgpath) 'imgpath
Me.Repaint

Exit Sub

ImageError: MsgBox ("Image error")

End Sub



Private Sub PutData()
'Subroutine to copy text entries from the display panel to the catalogue entry
'pointed to in the RowTxt box

Dim R As Long
Dim LastRow As Long

LastRow = FindLastRow

'Check that the text in the row number box is a legal number.
'If so, 'r' becomes the row number. Otherwise exit.
If IsNumeric(RowTxt.Text) Then
R = CLng(RowTxt.Text)
Else
MsgBox "Illegal row number"
Exit Sub
End If

'if r >1 but <= Last Row, copy the relevant display areas into their cell texts
'if r > Last Row, give fault message and exit.
If R > 1 And R <= LastRow Then

Cells(R, 1) = PlayName.Text
Cells(R, 2) = Author.Text
Cells(R, 3) = StartDate.Text
Cells(R, 4) = NoOfPerfs.Text
Cells(R, 5) = CircaDate.Text
Cells(R, 6) = StartTime.Text
Cells(R, 7) = TicketPrices.Text
Cells(R, 8) = Venue.Text
Cells(R, 9) = TheatreSocy.Text
Cells(R, 10) = PartOf.Text
Cells(R, 11) = Description.Text
Cells(R, 12) = CastCrew.Text
Cells(R, 13) = Notes.Text
Cells(R, 14) = References.Text
Cells(R, 15) = Files.Text
Cells(R, 16) = ThumbnailFile.Text


DisableSave 'Sub to disable Save and Cancel, since this sub is read only.

ElseIf R = 1 Then
ClearData 'Sub to clear display and exit, since you are looking at the caption row


Else 'r exceeds number of entries in catalogue, so fault exit
ClearData
MsgBox "Row number > number of entries"

End If

End Sub


Private Function FindLastRow()
' Finds last row - i.e. first row with first cell empty

Dim R As Long
Dim c As Integer


R = 2
c = 1
Do While R < 65536 And Len(Cells(R, 1).Text) > 0 'Loop down to first empty Col.1 cell
R = R + 1
Loop

TryNext: For c = 1 To 20 'Check that first 20 cells of line empty
If Len(Cells(R, c).Text) > 0 Then
R = R + 1
GoTo TryNext
End If
Next


FindLastRow = R

End Function


Private Sub ClearData()
' Subroutine to clear all cells in the display panel

PlayName.Text = ""
Author.Text = ""
StartDate.Text = ""
NoOfPerfs.Text = ""
CircaDate.Text = ""
StartTime.Text = ""
TicketPrices.Text = ""
Venue.Text = ""
TheatreSocy.Text = ""
PartOf.Text = ""
Description.Text = ""
CastCrew.Text = ""
Notes.Text = ""
References.Text = ""
Files.Text = ""
ThumbnailFile.Text = ""


End Sub


Private Sub DisableSave()
' Subroutine to disable the Save and Cancel command buttons when in read-only mode

SaveCmdBut.Enabled = False
CancelCmdBut.Enabled = False

End Sub


Private Sub EnableSave()
' Subroutine to enable the Save and Cancel command buttons when in read-write mode

SaveCmdBut.Enabled = True
CancelCmdBut.Enabled = True

If ReadVsEdit = 1 Then DisableSave 'EnableSave denied if Edit Control Box 'Read Only'

End Sub





'THE TEXT BOXES
'==============

'The user can always key on new information into any of the text boxes, but while Cancel and
'Save are disabled, this can never be entereed. So in all cases, all that has to happen is
'for any entry into any text window to trigger its _Change event and enable Cancel and Save.



Private Sub PlayName_Change()
'Play name

EnableSave

End Sub



Private Sub Author_Change()
'Author

EnableSave

End Sub



Private Sub StartDate_Change()
'Start Date

EnableSave

End Sub



Private Sub NoOfPerfs_Change()
'Number of performaneas

EnableSave

End Sub



Private Sub CircaDate_Change()
'Circa date - in effect the year

EnableSave

End Sub



Private Sub StartTime_Change()
'Start times of the performances

EnableSave

End Sub



Private Sub TicketPrices_Change()
'Ticket priees

EnableSave

End Sub




Private Sub Venue_Change()
'Venue

EnableSave

End Sub


Private Sub TheatreSocy_Change()
'Theatre society - e.g. dramsoc, grads, etc

EnableSave

End Sub


Private Sub PartOf_Change()
'What is a particular play part of - e.g. festival, evening of one-acts, etc.

EnableSave

End Sub


Private Sub Description_Change()
'Description - factual info about the show

EnableSave

End Sub


Private Sub CastCrew_Change()
'LIst of cast and crew and their roles

EnableSave

End Sub


Private Sub Notes_Change()
'Notes - e.g. library-type info, possible errors, etc.

EnableSave

End Sub


Private Sub References_Change()
'References

EnableSave

End Sub


Private Sub Files_Change()
'List of filenames of any files relating to the play - text or image

EnableSave

End Sub


Private Sub ThumbnailFile_Change()
'The filename of the image file that can be viewed from the user-form

EnableSave

End Sub


Private Sub Label12_Click()
'Treat as hyperlink
Dim Link As String

Link = ThumbnailTxt.Text
If Right(Link, 4) = ".jpg" Then Exit Sub 'Ignore click if this is a .jpg
If Left(Link, 5) = "http:" Then GoTo Viewit 'alredy a complete Web link

Link = ActiveWorkbook.Path & "/Filestore1840-1900/" & Link 'Create full path
On Error GoTo NoCanDo

Viewit: ActiveWorkbook.FollowHyperlink Address:=Link, NewWindow:=True

Exit Sub

NoCanDo: MsgBox "Cannot open " & Link

End Sub

[SamT]

I did a search of the code for anything that could be related to your issue but nothing popped up as the obvious problem.


imgpath = ActiveWorkbook.Path + "/Filestore1840-1900/" + imgpath
Link = ActiveWorkbook.Path & "/Filestore1840-1900/" & Link

Private Sub GetData could hold the problem. RowText is set by the Active sheet Vertical ScrollBar and returns a Row number that is used to find a cell with the path to an image file.

[SamT]

Post denied. New posts are limited by number of URLs it may contain and checked if it doesn't contain forbidden words.

I don't know, Maybe too many uses of the word "hyperlink" in the comments?

[JohnM73]

Thanks for going through it all, SamT. The two points I found were:

A: In Private Sub GetData() the line:

imgpath = ActiveWorkbook.Path + "/Filestore1840-1900/" + imgpath

refers to a folder: Filestore1840-1900 which is wrong. Likewise right at the end Private Sub Label12_Click() includes:

Link = ActiveWorkbook.Path & "/Filestore1840-1900/" & Link 'Create full path

which makes the same mistake. But I would expect them to generate a run-time fault message along the lines of 'Can't find file', not to generate link problems before the procedure even opens. I'll go on playing - hopefully I'll find something!

You have been very helpful and supportive - thank you!

[SamT]

When you have something fairly constant like that used in several places, I usually declare is as a Module level variable and set it in the Initialize sub. For fixed strings that are used in code, I always declare a
String Constant and use it in code. That makes it easier to maintain and avoids the use of Magic Strings.


Dim DataFolder As String

Sub UserForm_Initialize()
DataFolder = ActiveWorkbook.Path + "/Filestore1840-1900/"
End Sub


Const DataSubFolder As String = "/Filestore1840-1900/"

[SamT]

While we are on the subject of fixing your code. This is rather inefficient

Private Function FindLastRow()
' Finds last row - i.e. first row with first cell empty

Dim R As Long
Dim c As Integer


R = 2
c = 1
Do While R < 65536 And Len(Cells(R, 1).Text) > 0 'Loop down to first empty Col.1 cell
R = R + 1
Loop

TryNext: For c = 1 To 20 'Check that first 20 cells of line empty
If Len(Cells(R, c).Text) > 0 Then
R = R + 1
GoTo TryNext
End If
Next


FindLastRow = R

End Function


Private Function FindLastRow()
'Assumes that there is nothing in the Row below the desired Last Row

FindLastRow = Range("B1").CurrentRegion.Rows.Count
End Function

[JohnM73]

While we are on the subject of fixing your code. This is rather inefficient

Private Function FindLastRow()
' Finds last row - i.e. first row with first cell empty

Dim R As Long
Dim c As Integer


R = 2
c = 1
Do While R < 65536 And Len(Cells(R, 1).Text) > 0 'Loop down to first empty Col.1 cell
R = R + 1
Loop

TryNext: For c = 1 To 20 'Check that first 20 cells of line empty
If Len(Cells(R, c).Text) > 0 Then
R = R + 1
GoTo TryNext
End If
Next


FindLastRow = R

End Function


Private Function FindLastRow()
'Assumes that there is nothing in the Row below the desired Last Row

FindLastRow = Range("B1").CurrentRegion.Rows.Count
End Function

Thanks, SamT

Both last messages helpful, and I'll take you up on them. I agree about inefficient code - I'm afraid I'm rather geriatric, and learned my programming back in the days of the old procedural languages, so my use of modern coding languages is a weird mixture of bits of code found on-line, lots of trial and error, bits of macro-recording, and so on, so if you cast your expert eye over my code you will probably throw up your hands in horror! But it is not trying to do anything very clever, and so long as its ramshackle structure works and is clearly documented, I tend to leave it alone until something goes wrong!

I suspect that my problem arose because I developed my VBA on my original data-base worksheet, and then created new data-base worksheets of the same kind. But when I tried to copy the VBA into the new data-bases, I did something wrong, so that I somehow managed to embed not just the VBA, but also the data-base I was copying it from. Hence the link problem.

So now when I open Data-base B, work on it for a while, and then close it - lo and behold Data-base A appears and has to be closed down too, even though I didn't open it and it was not detectable as a separate worksheet or window. That is why I sent you the screen dump showing the Project Explorer panel, since I wondered if the problem was something at that level. I don't really understand the relationship between Excel worksheets and VBA procedures that works on them, so if I try to link a new worksheet to an old bit of VBA I'm working in the dark : pray2: !

What is the correct way to copy a VBA procedure from one Excel file to another?

[Cheeco]

Why does John M73 just provide a copy of the file so the users that provide there time to help others are not having to past miles of code to try to help. That would seem to make more sense to me. I apologize if I am in error on this one.

[SamT]

Maybe his workbook has proprietary or confidential information in it. So far I haven't needed to see the workbook or even the UserForm.

@ John,

What is the correct way to copy a VBA procedure from one Excel file to another?
There's two ways:

1) With both workbooks open, in the VBA editor, Project Explorer window, drag the UserForm or Module to the other "ThisWorkbook."

2) Export the Form or Module to the hard drive, then import it.

For code on a worksheet, or in ThisWorkbook, I usually just Copy and Paste.


I'm afraid I'm rather geriatric, and learned my programming back in the days of the old procedural languages,I'm 66 and played with CPM and Machine language in 81, learned Dos in 87, Pascal in 89 and stated with VBA in 2001


I don't really understand the relationship between Excel worksheets and VBA procedures that works on them,
I'm skipping a bunch of interesting stuff in the middle, but... Microsoft applications are Object oriented Programs, and VBA is an Object oriented Compiler scripting language. MS was very nice and set things up so that we can ignore the compiler and write "code" (scripts) as in any other high level language.

All MS applications are similar, but I will concentrate on Excel. The application is the parent Object, a Workbook is the next level object, followed by Worksheets and ChartObjects. The first Child of a Worksheet is the Range Object. Range(s) also have children, such as Font, Interior, Borders, et al.

All Objects have Properties, (Sheets.Count is a Workbook Property, Address a Range Property,) and Methods. Add is a common method for all Collections, Range has the methods Delete and Offset.

"Physical" Objects, (Cells, Worksheets, Workbooks, and the Application,) also have Events; Open, Close, Activate, BeforeSave, SelectionChange, Change, et al.

The 'most' Verbose way to reference a particular Range is

Application.Workbooks.("Book1.xlsx").WorkSheets.("Sheet1").Range("A1:B2")
99,999 times out of 100,000, you don't need the Application object, simply start with the Workbooks Collection.

If you are referring to a worksheet in the book that the code is in, and no other Workbooks are Active, you don't need to reference that Workbook Object (ThisWorkbook)

Worksheets.("Sheet1").Range("A1:B2")
If your code is in a Worksheet Module, you never have to reference to that Worksheet.

Range("A1:B2")

If you set a Variable to a Range, that variable always refers to a particular range on a particular Worksheet in a particular Workbook.

In a standard Module in a Workbook

Dim Rng1 as Range

In ThisWorkbook Module

Sub Workbook_Open()
Set Rng1 = Sheets("Sheet1").Range("A1:B2")
End Sub
In any code anywhere in that Workbook, Rng1 refers to Sheets("Sheet1").Range("A1:B2")

You can use it in other workbooks with MyVar = Workbooks("Book1").Rng1. If you set the Variable in the Sheet1 code page, you don't need to refer to the sheet; Set Rng1 = Range("A1:B2")

When writing code that references more than one Range, or one Sheet or one Workbook, it is best to assign them to Variables

Code in Book1.xlsm

Dim SrcBk As Workbook
Dim DestBk As Object 'The generic Object Type works, but takes more memory and CPU cycles
Dim SrvSht As Object
Dim DestSht As Worksheet
Dim SrcRng As Range
Dim DestRng as Object

Set SrcBk = ThisWorkbook 'Verbose version = Application.ThisWorkbook
Set DestBook = Workbooks("Book2.xlsx") 'Verbose version = Application.Workbooks("Book2.xlsm")

Set SrcSht = SrcBk.Sheets("Sheet1")
Set DestSht = DestBk.WorkSheets("Sheet2")
'Verbose version of previous line
Set DestSht =Workbooks("Book2.xlsx").WorkSheets("Sheet2"))

Set SrcRng = SrcSht.Range("A1:B2")
Set DestRng = DestSht.Range("C1:D2")
'Verbose version of previous line
Set DestRng =Workbooks("Book2.xlsx").WorkSheets("Sheet2").Range("C1:D2")



See the attachment for a comprehensive treatment of the Excel 97 Object Model.

[JohnM73]

In answer to Cheeko, I'm a newbie to this forum, so I did what I thought I was asked to do! But I agree that in retrospect there was a neater way to do it, so my apologies for cluttering up your screen. The worksheet itself is very uninteresting. It is a record of the history of student theater in a University - not specially 'confidential' but not of interest unless that is your 'thing' (if it is, feel free to get in touch)! It is just a flat table-type data-base with very numerous rows (one per theater production) and 17 columns, with text in each cell (dates, titles, names, etc.) - entirely passive - no 'spreadsheet' functionality at all.

The VBA code is a more or less standard user-interface 'view' that displays the content of any selected row in an easily viewable and editable form. The only slight elaboration is the optional capacity to view a .jpg or .pdf from a folder that is held beside the Excel file.

In answer to SamT, many thanks for the very detailed discussion of the spreadsheet/procedure relationship - that is very helpful. Re. 'geriatric' I'm 75, so I go back to pre-DOS, and pre-PCs and Apple - i.e. assembly code and paper tape - so I'm pretty close to the dinosaurs! That means that the switch from the old basically linear programming languages to the hierarchical object-oriented ones is a tough call for me - 'the basics' are vastly more complex. While I have some grasp of the very broad general principles, there is a lot of flailing in the dark. In the pre-object-oriented days, there would have been one file containing the worksheet, and a second file containing the procedure code. You would just link one to the other (probably a setting in the worksheet). Nice and simple!

But an advantage of being geriatric is that I have time to flail, and it is still very satisfying when something eventually works!

I'll have a go at removing my VBA code from my worksheet and reattaching it in the way you suggest, and see if that fixes it.

Thanks again for your time, help and patience.

[SamT]

I got to feed a paper tape routine into a PDP11 once. :D

BTW, ya old newbie fart, at this point in time you have %800 as many posts as Cheeco,

[JohnM73]

I've still got an old roll of 8-hole punched tape that I kept for nostalgia. Can't read it, of course - though I suppose one could try visually since each row of holes is an 8-bit character, but ....:banghead:

Cheers

[p45cal]

Did this one get resolved?
JohnM73, if you attach your file to your next post (reply, Go Advanced, Manage Attachments), we can probably give you a definitive answer/solution. You'll be able to delete it later if you want.

One thing did strike me as odd in your first screenshot; on the left (in the project explorer bit) you have a file called 1965-1970-Using-Proforma3.xlsx, and there's a userform (Userform1) in it! In my experience that counts as VBA even if there's no code, so I'd expect the file to end in .xlsm. Am I right to deduce that you had just put it there and the file hadn't been saved yet?

[JohnM73]

Did this one get resolved?
JohnM73, if you attach your file to your next post (reply, Go Advanced, Manage Attachments), we can probably give you a definitive answer/solution. You'll be able to delete it later if you want.

One thing did strike me as odd in your first screenshot; on the left (in the project explorer bit) you have a file called 1965-1970-Using-Proforma3.xlsx, and there's a userform (Userform1) in it! In my experience that counts as VBA even if there's no code, so I'd expect the file to end in .xlsm. Am I right to deduce that you had just put it there and the file hadn't been saved yet?

My apologies for the delay in responding - had to tackle another project. I agree about the xlsx/xlsm muddle - I must have accidentally saved it in the wrong format. The attached version of the file is now xlsm, and I've made some progress!

a) It is no longer operating by connecting to the copy of the VBA module PlayListForm held in another data-base xlsm. It seems to be free-standing. Hoorah!

b) As you can see in Project Explorer I have attached a copy of PlayListForm to this file, and if I double click on PlayListForm in the Project Explorer list, and then 'Run Sub/UserForm' from inside the Developer area, it works correctly! Also Hoorah!

c) However I can't get PlayListForm to be activated by the brown control button at the top right of the spreadsheet. I deliberately broke the link to disconnect it from the other data-base, but now when I right click on the button and try to 'Assign macro' there is no sign of PlayListForm. What am I doing wrong?

Any help gratefully received!

[SamT]

Delete that button and use the Controls Toolbox to insert another. Right click the button to name it (properties) and click it to insert a Code Stub for it in the Sheet's Code page.

Right click and choose "View Code." to edit the code to point to the Sub you want to run.

[JohnM73]

Delete that button and use the Controls Toolbox to insert another. Right click the button to name it (properties) and click it to insert a Code Stub for it in the Sheet's Code page.

Right click and choose "View Code." to edit the code to point to the Sub you want to run.


It now works! Hooray!

Seemed slightly different from your instructions (I'm still using Excel 2007 and/or I'm a bit cack-handed!) but quite enough to get me there, so this has all been very helpful. As always it takes ages to learn very simple things, so many thanks for your commitment and patience!

Now on to the next stage ..... :work: