Create a "helpsheet"

J

Jonsson

Hi all,

Is it possible to click a button and by that, run a code that make a
small "helpsheet" to pup up in the ordinary sheet.
I want to use the "helpsheet" for calculations, therefore I must have
that sheet to work just as a ordinary sheet, but smaller.

It could look like a big comment box, but must be able to handle
functions.

Any help or comment to this is highly appreciated

//Thomas
 
J

Jonsson

Hi ,

As I´m not so skilled in writing code, could you please give me cleare
instructions what to do?

Thanks in advance

//Thoma
 
J

Jonsson

Hi Harald,

I,ve checked them out and thats not what I need, sorry to say.
I have posted a bit of a code that I got from Tom O, but beeing
newbie on VBA, I did´nt really understand what he meant.

res = Application.Inputbox("enter your formula")
msgbox res

Hope someone can help me!!

//Thoma
 
T

Tom Ogilvy

Sub ShowBox()
res = Application.Inputbox("enter your formula")
msgbox res
End Sub

in the inputbox enter your formula as you would in a cell (including
starting with an equal sign), then click OK. A msgbox should then appear
with the result.

If this does what you want, then it can be explored further. If not, then
no need to waste any time on it.


Put the above code in a general module - in the VBE (alt+F11), from the
menu, choose insert module. Paste the code in the resulting module. go
back to Excel (alt+F11). go to tools=>Macro=>Macros, select ShowBox and
click run.


for example, I ran it and entered:

=(sqrt($C$4)*31/1000)^.5

I entered the C4 by selecting it on the sheet using the mouse with the box
visible.

the message box showed the evaluated/computed result.
 
B

Bill Renaud

Try the following subroutine to pop up a small workbook near the active
cell:

Sub MakePopupWorkBook()
Dim rngActiveCell As Range

'Change the current workbook to a normal window.
ActiveWindow.WindowState = xlNormal
ActiveWorkbook.Windows.Arrange ArrangeStyle:=xlTiled

'Now get a reference to the active cell.
Set rngActiveCell = ActiveCell

'Create a new workbook at 75% size (.Zoom = 75).
Workbooks.Add Template:="Workbook"
With ActiveWindow
.WindowState = xlNormal
.Zoom = 75
.Width = 250
.Height = 200
'Position the popup worksheet at the
'lower right corner of the active cell.
.Top = rngActiveCell.Top + rngActiveCell.Height + 30
.Left = rngActiveCell.Left + rngActiveCell.Width + 25
End With
End Sub

It positions the new workbook approximately at the lower right corner of the
active cell, which may be good enough for what you want to do. Is this along
the idea of what you are looking for? Are you going to manually paste the
value back into the original worksheet when done?
 
J

Jonsson

Hi,

Thanks for helping me out!

I have a sheet in the mainwoorkbook called "counting" that I would lik
to pop up just as you suggested in the erlier post.
If that is possible, it would be great, otherwise not. It won´t work t
create a new woorkbook.

Is it possible and if, how do I do it?

//Thoma
 
J

Jonsson

Hi,

I´m sorry to say, but I get an error at "res" as a "undifine
variabel". I´m using swedish version.

Anyway, that solution could work, but is it possible to set the box t
automatically do the calc. from 100/60 to count time?

(e.g input in box 10,15 clocktime and the box return 10,25 in th
active cell)?


//Thoma
 
H

Harald Staff

Sure

Sub ShowBox()
Dim res As String
Dim D As Date
On Error GoTo TheEnd
res = Application.InputBox("enter your decimal time:")
If res = "" Then Exit Sub
D = CDbl(res) / 24
ActiveCell.Value = D
ActiveCell.NumberFormat = "hh:mm"
TheEnd:
End Sub

HTH. Best wishes Harald
 
J

Jonsson

Hi Harald!

This was just what I needed!!

Works absolutely great, but can you help me to do vice versa (e.
clocktime 10,15 in box and convert that into decimals 10,25?

Thanks, you´ve already saved my day, I´m really greatful.

//Thoma
 
T

Tom Ogilvy

Sub ShowBox()
Dim res as Variant
res = Application.Inputbox("enter your formula")
if not iserror(res) then
msgbox res
else
msgbox "Bad formula"
End if
End Sub

You said you wanted a popup box that does calculations. If you just want to
query the user for input, and have your code do a specific calculation, the
you should have said that from the beginning.
 
T

Tom Ogilvy

Sub ShowBox1()
Dim res As String
Dim D As Double
On Error GoTo TheEnd
res = Application.InputBox("enter your time (ex: 10:42):")
If res = "" Then Exit Sub
D = CDbl(CDate(res)) * 24
ActiveCell.Value = D
ActiveCell.NumberFormat = "#.00"
TheEnd:
End Sub
 
J

Jonsson

Harald and Tom!

Thank you so very much for giving me such good guideness an
support!!!

This works splended!!!

//Thoma
 
B

Bill Renaud

Public Sub PopupCountingWorksheet()
Dim rngActiveCell As Range
Dim lngCellTop As Long
Dim lngCellLeft As Long

ActiveWindow.WindowState = xlNormal
ActiveWorkbook.Windows.Arrange ArrangeStyle:=xlTiled

'Save position information for later.
With ActiveCell.Offset(1, 1)
lngCellLeft = (.Left + 30) * ActiveWindow.Zoom / 100
lngCellTop = (.Top + 25) * ActiveWindow.Zoom / 100 + 10
End With

ActiveWindow.NewWindow 'Create new (popup) window.

'Show the "counting" worksheet in the new window.
ActiveWorkbook.Sheets("counting").Select

'Position the popup worksheet at the
'lower right corner of the active cell.
With ActiveWindow
.Zoom = 75 'Size the new window at 75%.
.Width = 250
.Height = 200
.Left = lngCellLeft
.Top = lngCellTop
End With
End Sub

How is the "counting" worksheet involved in converting time from one format
to another ("hh:mm" to "hh.hh") as per your other posts?

It sounds like you are doing multiple things with this application.
 
Top