Tons of questions

  • Thread starter Thread starter famdamly
  • Start date Start date
F

famdamly

The help feature in excel is not always helpful enough. I've just bee
trying to figure everything out as I go. There are some things I hav
to underrstand to proceed with my design.

1. I want to know if I can make a custom right click shortcut menu. I
there a way to do this with excel? There should be.


2. I made forms with huge fonts and fields. How do I get data that i
input into these fields to also be compiled into a huge list on anothe
worksheet. On that same note, beings I'm working with multiple fields i
multiple forms and some left blank, I want to be sure that the data fro
each field that corresponds with each particular job or customer doesn'
get mixed up or overlapped with other data because of the blanks. Tha
is if I use some kind of counter to slip into a formula for transferin
the data into the other worksheet. Which reminds me of the other proble
I encountered. I wanted to use a cell reference variable in a simpl
formula. Like c5=a(d5) So if d5=7 then c5 will equal what a7 does
I'm probably missing a symbol or two in the formula.


3. Can you run a macro from an if. The reason i ask is- I'm togglin
text when a button is pushed on a form next to the field. The toggl
button in controls I couldn't figure out a way to do it.


4. In the drawing part of the program. Is there a way to make a shap
within a shape and lock it in to that other shape so when you drag th
big shape around the ones locked in move right along with it.

5. Also if you make a rectangle is there a way to automatically attac
the formulated dimensions onto the rectangle. Also there should be
way to name the object/shape and have that lock in too. I take it th
macro is the way to accomplish this.

Thanks for answering any of these questions
 
famdamly said:
1. I want to know if I can make a custom right click shortcut menu. Is
there a way to do this with excel? There should be.
Click to expand...

Yes. An example.

Public Sub addMenu()
removeMenu 'just in case
With Application.CommandBars("Cell")
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = "Routine1"
.OnAction = "Another"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "Routine2"
.OnAction = "YetMore"
End With
End With
End Sub


Public Sub removeMenu()
On Error Resume Next
With Application.CommandBars("Cell")
.Controls("Routine1").Delete
.Controls("Routine2").Delete
End With
On Error GoTo 0
End Sub

2. I made forms with huge fonts and fields. How do I get data that is
input into these fields to also be compiled into a huge list on another
worksheet. On that same note, beings I'm working with multiple fields in
multiple forms and some left blank, I want to be sure that the data from
each field that corresponds with each particular job or customer doesn't
get mixed up or overlapped with other data because of the blanks. That
is if I use some kind of counter to slip into a formula for transfering
the data into the other worksheet. Which reminds me of the other problem
I encountered. I wanted to use a cell reference variable in a simple
formula. Like c5=a(d5) So if d5=7 then c5 will equal what a7 does.
I'm probably missing a symbol or two in the formula.
Click to expand...

I was fine until I read the last sentence which makes no sense to me at all.

3. Can you run a macro from an if. The reason i ask is- I'm toggling
text when a button is pushed on a form next to the field. The toggle
button in controls I couldn't figure out a way to do it.
Click to expand...

No. You can create your own VBA functions that return a result to th calling
cell, but change nothing else.

When you say form, do you mean a userform, or are you referring to something
designed on a spreadsheet?
4. In the drawing part of the program. Is there a way to make a shape
within a shape and lock it in to that other shape so when you drag the
big shape around the ones locked in move right along with it.
Click to expand...

You can group shapes, which means they are effectively locked together.
5. Also if you make a rectangle is there a way to automatically attach
the formulated dimensions onto the rectangle. Also there should be a
way to name the object/shape and have that lock in too. I take it the
macro is the way to accomplish this.
Click to expand...

VBA for sure.

Dim myShape As Shape

With ActiveSheet.Shapes
Set myShape = .AddShape(msoShapeRectangle, 239.25, 133.5, 135#,
71.25)
myShape.Name = "Shape4Bob"
End With
 
Bob you rock:)
You must really know your stuff.
I really appreciate the help. I will delve into the right click menu
stuff later, it's way over my head. Atleast I know it's possible.

The grouping thing, that's great. Maybe if I just read the instructions
I would have gotten that.:)
When you say form, do you mean a userform, or are you referring to something
designed on a spreadsheet?
Click to expand...
The latter. Now I'll have to read up on userform.

I don't know VB at all. As a kid I used basic alot and got pretty
creative with that. I'm just trying to get back into it. I notice some
similarities. What I've been doing is using macros and then I look at
the VB to see what kind of tweaks I can do. This is alot of fun.

I think Kleev deciphered my sentence. Thanks Kleev, I will give that a
try.
 
You will probably find lots of similarities in the Basics, but with Excel
the important thing is to understand the object model. The Object Browser,
F2 in the VBIDE, will help you explore that, as will selecting an object in
code, such as Range, then F1 directly into Help.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
BTW, if you want to explore userforms, some reading for you


http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm
Lesson 11: Creating a Custom Form
Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step.

http://support.microsoft.com/default.aspx?kbid=161514
XL97: How to Use a UserForm for Entering Data

http://support.microsoft.com/default.aspx?kbid=213749
XL2000: How to Use a UserForm for Entering Data

http://support.microsoft.com/default.aspx?kbid=168067
File Title: Microsoft(R) Visual Basic(R) for Applications Examples for
Controlling UserForms in Microsoft Excel 97

Peter Aiken Articles:
Part I
http://msdn.microsoft.com/library/en-us/dnoffpro01/html/IntroductiontoUserFormsPartI.asp

Part II
http://msdn.microsoft.com/library/en-us/dnoffsol02/html/IntroductiontoUserFormsPartII.asp


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Back
Top