Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

fixing error permission denied"populate data into listbox"

0

hello  

i  search    so  much  in the  internet     about  solve  my  problem   so   i  hope    to  find the  solution  here    i  face  " run time  error   permission   denied  i  desighn  the  userform    i   would   when  i   select   from  combobox    and   write   the   data   in  textbox1   then populate   data  in listbox   based  on  data in sheet1 

Answer
Discuss

Answers

0

"Permission denied" isn't an Excel problem. It refers to the permission you need to access files on your computer. Such permissions are controlled by Windows and set in Windows Explorer.

Take a look at this link to learn more.

Edit Jul 11 2020   ============================

The line of code that throws the error, .Column = sRng.Value, attempts to read from a worksheet and the permission is denied.  That matches my suspiction exactly. However, it's true that VBA will sometimes not give the correct reason for an error. So, what to do if the error is caused by the code?

In fact, there is plenty of reason to suspect this to be the case. The column to which you wish to write isn't specified, and if it were, and presuming that it exists, you can't assign a worksheet column to a combobox column. You might try

ComboBox1.List = sRng.Value

.

However, while it's lovely to behold when it works, assigning an array to a ComboBox's List property doesn't always work Miscrosoft recommends to use the Add method and build the List item by item. Reading from an array you can do this in a loop, and it's very fast. But if you want to continue trying to assign the array directly, note that sRng.Value is a 2D array even if the range comprises of only a single row or column. You can quickly convert such an array to a 1D array using Excel's Transpose function.

Dim MyArray As Variant
MyArray = Application.Transpose(sRng.Value)
ComboBox1.List = Application.Transpose(MyArray)

You may need to transpose only once, depending upon whether your range is a row or a column. Try it out. While this method may not solve your problem but it will definitely clarify the issue. If your code is lacking a permission to read from the worksheet the same error will now occur on this line: MyArray = Application.Transpose(sRng.Value). On the other hand, if the lacking permission refers to writing to the ComboBox column you will now have a completely different kind of error which you can avoid by using the Add method.

Discuss

Discussion

" hi, variatus     i  follow   the steps   as  in  the  article   i  don't   find   anything  changed  in  my  computer setting   and   it   doesn't  help  me    also   througout  i  search  the internet  i  found  most  of  people   this  problem   like   me   and   the  problem  is   in  the  code   not  setting   window  as  you  said    and  highlight    the error in  this   line  ".Column = sRng.Value"  and   the  error   is  this " run time  error 70"  permission denied  i hope  find   way   to  solve   my  problem   
leopard (rep: 88) Jul 10, '20 at 6:55 am
actually   i tested your suggestion  about  ComboBox1.List = sRng.Value  it doesn't  work   the  same problem still continues 
leopard (rep: 88) Jul 14, '20 at 8:54 am
The Add method always works.
Variatus (rep: 4889) Jul 14, '20 at 10:15 pm
so  what suggest  for  this  problem    actually   this  code  i  copy  from another file  and  it  works   but  when  i  adjusting  for  my  file  it  shows the error  i followed   the  code  but  i  can't  where is needing adjust 
leopard (rep: 88) Jul 17, '20 at 5:31 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login