Solved Store Variable from InputBox and use in Another Sub

Microsoft Word 2013 32/64-bit
May 8, 2019 at 01:40:27
Specs: Windows 7 x64, 2,4 GHz / 4 GB

I want to add 2 buttons in the QAT and press button1 to set a value for row height (eg 28), then press button2 to set the height of the active row to the previously set value (28).

I will not use consecutive height adjustments.
- I press button1 and set the height and store the value.
- Then I press button2 for one row, then do some text editing in another row, then move to a 3rd row and press button2 again if it is required to set the row height. And so on.
- If I want to change the height, I click button1 again and store a new value.

I thought to use 2 Subs for this:
- 1st with an InputBox to insert the desired value manually and store/memorize it for later use
- 2nd to set the row height using the value set before

I wrote the code below but as soon as the first code ends the value of h becomes null. Of course I must be doing something wrong or not doing it at all... but I don't know what.

Public h as Integer

Sub DefineHeight()
  h = InputBox("Insert row height")
End Sub

Sub SetHeight()
With ActiveSheet.Rows(ActiveCell.row)
    .RowHeight = h
End With
End Sub

Could someone please help me understand if it's even possible and how to do it?


See More: Store Variable from InputBox and use in Another Sub

Report •

#1
May 8, 2019 at 03:39:02
✔ Best Answer
It is true that the variable will be cleared when the sub ends.

One way to retain the value is to have the code write the value into a cell and then have the SetHeight macro reference that cell.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#2
May 9, 2019 at 02:08:21
I will do just that. Thanks for the tip!

Edit:

Ok, done, and in case someone else is interested in the resulting codes I add them below, but first I will explain what they do:

Button 1 in QAT:

The code creates a new very hidden worksheet in the active workbook, if one does not already exist.
An InputBox asking for the desired row height appears and then the value is stored in Worksheets("VeryHidden").Range("A1").

Button 2 in QAT

The code extracts the value stored in Worksheets("VeryHidden").Range("A1") and changes the height of the row where the active cell is located.

I press button 2 for the same adjustment everywhere I need it, then press button 1 only when I want to change the height.

Option Explicit
Public h As String
Public rng As Range
Public Ws As Worksheet

' button 1 - create very hidden ws and store value in it
Sub DefineHeight()

For Each Ws In Worksheets
    If Ws.Name = "VeryHidden" Then
        GoTo SetRange
    End If
Next

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "VeryHidden"
Set Ws = Worksheets("VeryHidden")
    Ws.Visible = xlSheetVeryHidden

SetRange:
Set rng = Worksheets("VeryHidden").Range("A1")

    h = InputBox("Insert row height")
    
If IsNumeric(h) Then
    rng.Value = h
Else
    MsgBox "You haven't entered a number"
    Exit Sub
End If
End Sub

' button 2 - extract that value and adjust row height
Sub AdjustRowHeight()
Set rng = Worksheets("VeryHidden").Range("A1")

With ActiveSheet.Rows(ActiveCell.row)
    .RowHeight = rng
End With
End Sub

' button 3 - optional, if you want to delete the VeryHidden sheet
Sub DeleteHidden()
Application.DisplayAlerts = False

For Each Ws In Worksheets
    If Ws.Name = "VeryHidden" Then
        Ws.Visible = xlSheetHidden
        Ws.Delete
    End If
Next

Application.DisplayAlerts = True
End Sub

message edited by Mrrrr


Report •

#3
May 9, 2019 at 13:21:57
FYI...

You do not need to loop through the sheets to determine if a specific sheet exists. You can ask Excel to look for it directly via ISREF.

In addition...

If you use the Application.InputBox method, you can allow the user to Cancel cleanly.
If you specify a Type for the InputBox, you don't have to check for a "number", VBA will do it for you.

Finally, you appear to have used SetRange as a label and I'm not sure why. Labels are usually used in conjunction with a GoTo statement. If you meant for SetRange to be a comment, then you should use the standard 'SetRange notation.

When you create a label, the VBA compiler has to do extra work. You'll probably never notice the difference, but it's a bad habit to use labels as comments.

Sub DefineHeight()

'Add sheet if VeryHidden doesn't exist
  If Not Evaluate("ISREF('" & "VeryHidden" & "'!A1)") Then
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "VeryHidden"
     Set Ws = Worksheets("VeryHidden")
     Ws.Visible = xlSheetVeryHidden
  End If

   Set rng = Worksheets("VeryHidden").Range("A1")

   h = Application.InputBox("Insert row height", Type:=1)
    
'Allow cancel or use value
   If h = False Then
      Exit Sub
   Else
      rng.Value = h
   End If
   
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

Related Solutions

#4
May 13, 2019 at 04:16:40
Thanks for the tips and tricks!
I still have to learn a lot in VBA and with your help I am doing just that!

Thanks again!


Report •

Ask Question