+ Reply to Thread
Results 1 to 3 of 3

where do I add code for hiding/unhiding fields based on a user input value

  1. #1
    Registered User
    Join Date
    04-20-2020
    Location
    Ottawa
    MS-Off Ver
    7.1
    Posts
    3

    where do I add code for hiding/unhiding fields based on a user input value

    Hello

    I am totally new to VBA so please excuse my lack of basic knowledge AND I hope I am explain my issue clearly.
    Fell free to give me pointers on shortcuts and writing better code
    Thanks in advance

    I would like my code to do the following:
    If the value of 'Total' > 0 then I would like to unhide fields. If it is 0 then hide fields.
    My workbook has multiple woksheets

    My questions is

    1. where do I put this code in VBA. Using trial and error in different macro places.
    Sub Worksheet_Change(ByVal Target As Range)

    2. keep getting syntax errors or my code does not run or is 'out of range'

    3. Right now I created a button and it works when user clicks on button but I would like to change it to work when the user enters a 'Total' > 0
    Code:

    Sub Btn1_Click()
    If ThisWorkbook.Worksheets("Sheet_stuff").Range("J59").Value = 0 Then
    ThisWorkbook.Worksheets("Sheet_stuff").Range("B:P").EntireRow.Hidden = False
    ThisWorkbook.Worksheets("Sheet_stuff").Rows("61:70").EntireRow.Hidden = True
    Else
    ThisWorkbook.Worksheets("Sheet_stuff").Range("B:P").EntireRow.Hidden = False
    ThisWorkbook.Worksheets("Sheet_stuff").Rows("61:70").EntireRow.Hidden = False
    End If
    End Sub

    Bonus: I would like to dynamically create rows based on the total entered instead of hiding and displaying fields then ie. If total = 3 then insert 3 rows for user to input

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,449

    Re: where do I add code for hiding/unhiding fields based on a user input value

    Hi hdean and welcome to the forum,

    There are a few different kinds of "Modules" in VBA. A "Module" is where the VBA code goes. It can be behind a worksheet, workbook, userform or standard module. Read about them with pictures at:
    https://exceloffthegrid.com/private-vs-public-in-vba/

    Writing VBA code is like flying a plane. It isn't easy and the learning curve is pretty steep, but don't expect to know it all in a few weeks. Knowing where you code goes is a good start.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    04-20-2020
    Location
    Ottawa
    MS-Off Ver
    7.1
    Posts
    3

    Re: where do I add code for hiding/unhiding fields based on a user input value

    Thank you Marvin
    that was very helpful.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Please Help - Hiding/Unhiding code - MACRO/VBA
    By dy137 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-17-2017, 12:45 AM
  2. VBA Code help to set up user input fields
    By dwspencer in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-16-2015, 03:41 PM
  3. Hiding rows based on a user input box
    By jshaw82 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-08-2013, 06:34 PM
  4. [SOLVED] VBA - Condense code for hiding/unhiding cell based on value in cell above
    By arundh in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-02-2013, 03:19 PM
  5. [SOLVED] Need help - VBA code for hiding / unhiding rows through Option Button
    By gm2612 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-31-2013, 03:26 AM
  6. VBA code in HIDING & UNHIDING row if value is zero
    By znalron@yahoo.com in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-26-2013, 11:53 PM
  7. error comes in my code for hiding/unhiding
    By ss_bb_24 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-07-2008, 05:09 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1