Create a "helpsheet"

[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

 

[Tom Ogilvy]

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

 

[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

 

[Harald Staff]

A micro-spreadsheet, fully customizable, in a small pop-up form, returning a
single value into a cell ? It is close to impossible. (Ok, emphasis "close
to" then. It would involve a Userform, the Web Spreadsheet component, lots
of clever event programming,... )

Would a popup calculator do the job ? See
http://cpap.com.br/orlando/#Calculator
http://j-walk.com/ss/excel/files/toolbarcalc.htm
for a couple of solutions.

HTH. Best wishes Harald

 

[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

 

[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.

 

[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?

 

[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

 

[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

 

[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

 

[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

 

[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.

 

[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

 

[Jonsson]

Harald and Tom!

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

This works splended!!!

//Thoma

 

[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.