Data Form used in another worksheet

R

Rick Tidd

I want to use a data form in say worksheet1, with the data residing in
worksheet3. Is this doable, or will I need to do the form in VBA?
Thanks
Rick Tidd
 
D

Dave Peterson

Maybe you could put a button from the Forms toolbar on sheet1 that uses the data
on sheet3 and displays the data|form dialog.

Option Explicit
Sub testme01()
Worksheets("Sheet3").ShowDataForm
End Sub

maybe sufficient.
 
R

Rick Tidd

Thanks very much for your help. I no almost nothing about VBA, however with
your help, I was able to make this work.
Thanks again.
Rick Tidd
 
R

Rick Tidd

This worked fine on a sample worksheet, once I tried it on my existing one, I
got the following message:
Compile Error:
Invalid outside procedure.

Is there an easy fix for this?
Thanks
Rick
 
D

Dave Peterson

The new macro you created has an error in it.

Maybe you could just copy from the original suggestion and try again or copy
what you used and paste into your reply.

Be sure to indicate the line that failed, though.
 
R

Rick Tidd

My Macro is written:
Sub Pricelookup()
Option Explicit
Sub testme01()
Worksheets("Alpha").ShowDataForm
End Sub


When macro stops and I get error message, this is what is on the screen:
With Worksheets(1).Range("a2:a3000")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

Again, thanks for the help, I know almost nothing about VBA.
Rick
 
D

Dave Peterson

First, the "Option Explicit" lines belongs outside your procedure--at the top of
the module.

Second you have a couple of routines jumbled up into one.

Maybe...



Option Explicit
Sub testme01()
Worksheets("Alpha").ShowDataForm
End Sub

Sub Pricelookup()
With Worksheets(1).Range("a2:a3000")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub

But it looks like that "Pricelookup" procedure was an experiment that is just
left over. In fact, it looks like it was copied and pasted from VBA's help.

I think I'd delete that routine and just use the top stuff:

Option Explicit
Sub testme01()
Worksheets("Alpha").ShowDataForm
End Sub


Rick said:
My Macro is written:
Sub Pricelookup()
Option Explicit
Sub testme01()
Worksheets("Alpha").ShowDataForm
End Sub

When macro stops and I get error message, this is what is on the screen:
With Worksheets(1).Range("a2:a3000")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

Again, thanks for the help, I know almost nothing about VBA.
Rick
Click to expand...
 
R

Rick Tidd

Dave,
Thanks for all your help. I tried starting over with a copy of the top
macro here and I still get the same message. I think I will try to find
someone to write this for me. IIf you have any interest, give me a quote.
you can reach me at madhatter@(remove this here)hatter-mad.com.
Thanks
Rick Tidd
 
D

Dave Peterson

I would remove existing code from the module and just paste that short code into
the module.



Rick said:
Dave,
Thanks for all your help. I tried starting over with a copy of the top
macro here and I still get the same message. I think I will try to find
someone to write this for me. IIf you have any interest, give me a quote.
you can reach me at madhatter@(remove this here)hatter-mad.com.
Thanks
Rick Tidd
Click to expand...
 
Top